Excel Statistics 55: Probability Pivot Table

Say you have a table (5x5, for argument's sake)-- along the top is
demand from customer A, on the bottom is demand for customer B. Say the
legend for both the rows and the columns is (0,1,2,3,4), and in the
table itself is the corresponding probability for each pair.

In other words, if you want to see the probability that demand from
both customers was zero, you'd look in the upper left corner of the
array. If you wanted to see the probability that customer A demanded 2,
and customer B demanded 3, you'd go down 3, and over 4 (remember that
0,0 is upper left, not 1,1).

Without using a macro, or manually writing out sum commands, is there a
way to create a total probability distribution table from the table I
have given you? In other words, we know that minimum demand is 0, and
that sum is given by the upper left cell, but the probability that the
total demand is 3 is the sum of cells (0,3), (1,2), (2,1), and (3,0).
The table in this example would be 9 wide, with the probability of 0 on
the left, and 8 on the right.

Perhaps there's a way to match a VLOOKUP, and HLOOKUP, and a few IFs,
maybe, but if anyone can suggest a more automated way or a clean
syntax, I'd be grateful.

Thanks!

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.

Hi,

I have a column of probabilities, I need to create a function that can multiply all of the probabilities by each other to the degree required and then sum them. For example; I need to find the sum of (A1*A2) + (A1*A3) + (A2*A3) or I may need all the possible trebles attainable from the probabilities grid or all the possibilities of 4 numbers multiplied together.
Is this possible?

Any help would be greatly appreciated!

Thanks in advance

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!