Hello,
This is my first post on this forum, I hope I will not be too confusing. Don't hesitate to ask me if things are not clear.
I have attached an example so it will be easier to follow what I am trying to explain.
I work in genetics and I do analysis reports of different fragments for a gene. Each fragments can have 0, 1, or several variations.
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP)
At the bottom of this table, I would like to have a cell with the average % of success for all fragments.
The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
The points I have to consider for this macro is that the number of fragments and number of samples change from one analysis to another, so it would be nice if the macro would be able to count that automatically. It would also be nice if the generated statistics table could have the same formatting as I use on the "statistics" table.
My excel document already includes some automated calculations and statistics that I programmed myself, but I really have no idea on how to do what I explained above. I know it is probably a lot of VBA encoding, so I thank you in advance for your help.
Sincerely
David
p.s. This is not an emergency, as I am of course able to do the statistics manually, but it would definitely be nice and time-saving. Once again, thanks a lot.
I have a set of macros in a template/starter file for users to open and start a project. Within this workbook contains a bunch of macros. Instead of sending everyone a new workbook with updated macros each time, I would like to host these macros or somehow distribute updated macros to everyone efficiently.
I am unsuccessful in finding a way to have a button call another macro from a workbook on a network drive.
I am also unsuccessful in using an addin. I have saved the macros as an add-in and added it to my workbook, but am unable to call those functions.
I could also just export the modules each time one updates but then I have to rely on the user to replace their current one which I don't want to do.
Essentially, I would like the update to occur without relying on the user to do very much or anything at all.
Thanks
Hi,
I have created a graph from pivot table (see example attached), I would like to get a legend beside it with statistics data such as mean std dev etc.
Can it be done in excel ?
Thanks
Hi,
I have a pivot table A created in Excel 2003 with the data source linked to another pivot table B. How do I change the data source of pivot table A link to a new pivot table C in Excel 2007? Can Excel 2007 create/change pivot table with data source link to another pivot table? I can't seem to find any function for that under Excel 2007 Pivot table commands. How do I resolve this? Please help.
Thanks!!
Hi,
I have a pivot table A created in Excel 2003 with the data source linked to another pivot table B. How do I change the data source of pivot table A link to a new pivot table C in Excel 2007? Can Excel 2007 create/change pivot table with data source link to another pivot table? I can't seem to find any function for that under Excel 2007 Pivot table commands. How do I resolve this? Please help.
Thanks!!
Hi,
I have a pivot table A created in Excel 2003 with the data source linked to another pivot table B. How do I change the data source of pivot table A link to a new pivot table C in Excel 2007? Can Excel 2007 create/change pivot table with data source link to another pivot table? I can't seem to find any function for that under Excel 2007 Pivot table commands. How do I resolve this? Please help.
Thanks!!
Not Solved, but found a different way of doing it which I now need advice on, lol.
I have a table of contents page. The TOC is updated each time some one opens the sheet (this is a must have based on the requirements). I'm trying to include some statistics next to each item in the TOC. These statistics are on each worksheet and just need to be copied to the TOC.
Now my problem.
The worksheets are NOT keep in alphabetical order, so After the TOC is created a sort is done to put the TOC links in alphabetical order. After the order is set, I need a formula that will read the worksheet name from a cell on the TOC (which is really a hyper link to a worksheet).
I have this already:
ActiveCell.FormulaR1C1 = "=Address!R[-2]C[-2]"
If "=Address!" (Where address is the name of one of the sheets) could be replaced with a cell reference that (I think) would do the trick.
My TOC is a MACRO not a cell formula so if this can not be done with a cell formula but can be done through VBA that would be fine.
Hi,
I have created a table (TABLE 1) of many metrics associated with different team performances on a daily basis. As a simple example:
Date/Team/Start_Time/End_Time
For simplicity's sake let's say I have 2 teams that work everyday. In a seperate spreadsheet I have created a table (TABLE 2) with compiled average statisitcs for each team over periods of time. For example
TABLE 2
Team A (8/1/2010-8/31/2010)
Avg_Start_Time 9:01:34
Avg_End_Time 5:03:06
Team B (8/1/2010-8/31/2010)
Avg_Start_Time 8:58:30
Avg_End_Time 4:59:00
Currently I am using the formula =SUBTOTAL(101,'All Data'!$K$5:$K$69) to create a calculator (TABLE 3) that automatically changes when I filter the data in TABLE 1(e.g. Team A or Team B), then copy and special paste the average statistics to TABLE 2 to compare each groups performance.
I want to automate the model so I don't have to change the filter for each team then copy and paste the statistics. That is, I want to create a formula within TABLE 2 that automatically only queries the average statistics of TABLE 1 filtered for each team, making TABLE 3 and copying and pasting uneccessary.
Thanks is advance for the help!
Hello everyone,
I have a table with two coloumns, first is data and second is frequency, how to do descriptive statistics for this.
Please help me i am struck with this.
thanks
