Dynamic statistics

[beternal]

Hi all,

Please find attached 'pseudo-sheet'.

Essentially I have a table of data with many columns. Elsewhere I have an xlookup to bring over specific columns using a dropdown list.

I want to DYNAMICALLY run a T-test on the gathered data from the dropdown results.

i.e. in the attached sheet,
-If I select '1' in K5, it pulls up data below from column B <---happy with this
-If I select '2' in L5, it pulls up data below from column C <---happy with this

Using the Analysis ToolPak add-in, I can then create a t-test table from the resultant data now in K5:K23 and L5:L23 ...however when I update the data in those cells (using the dropdown) the t-test does not dynamically update

Can this be done?

Thanks in advance

[MrShorty]

Yes, this can be done. Each of the outputs in the analysis tool pack's t-test output can be calculated using various Excel functions and formulas. I don't recall offhand how to calculate all of them (I am hoping you have enough knowledge of statistics to answer that part of the question), but I know they can all be calculated using worksheet functions and formulas. For example, the mean for each is simply the AVERAGE() function. The variance for each is the VAR() function. The pooled variance is a weighted average of the two variances =SUMPRODUCT(O8:P8,O9:P9)/SUM(O9:P9), and so on through the rest of the output table. In case you are unaware, Excel has several built in functions related to the t distribution (T.DIST(), TTEST(), and so on -- see list of statistical functions here: https://support.microsoft.com/en-us/...rs=en-us&ad=us ) that will probably be useful as you recreate the calculations performed by the analysis toolpack.

From there, are there specific formulas you need help with?

[beternal]

lol, I stupidly forgot I could do it the long way! - yeah I can do it this way... but if there is a dynamic way anyone can recommend, that would be very much appreciated! - I'll work on the slow way in the meantime