Statistically significantly higher than average

**konradz** · 05-16-2013, 12:43 PM

Dear Colleagues,

I've the following data table:

Group	Case	Variable 1	Variable 2	Variable 3	...
Group A	C0001	545646	56465456	3	...
Group A	C0002	878946	5645465456	13	...
Group A	C0003	456456	2123	321231	...
Group B	C0004	545646	131321	54564	...
Group B	C0005	5656	1311	564	...
...	...	...	...	...	...

I need to make the following statements about each case:

Given case is statistically significantly higher than average
Given case is statistically significantly lower than average
Given case is not statistically significantly different from the average

I have to merge the statements in one table that will be dynamically colour coded depending on the selected group. I have defined a number of dynamic offset formulas:

case_value - returns value for each case from the table depending on case number and variable number
group_values - return the values for the whole (Group A, B, etc.) depending on the selected variable
variable_value - returns the variable values for whole column depending on the selected variables

What I presently need is to develop a formula that will take value 1 for cases that are statistically significantly higher than average, 2 for cases that are statistically significantly lower than average and 0 for cases that are not not statistically significantly different from the average. I have no problem with devising additional lookup and offsets formulas that will pull required strings of data from the data table. What I would like to know is how I get from numerical data to underlined logic formula that will return the value depending on the characteristics of statistical significance. I came across this helpful page, but unfortunately, it's not enough for me. Needles to say, I will appreciate any help!

**tigeravatar** · 05-16-2013, 01:20 PM

konradz,

What average are you calculating? Average of GroupA/Variable1? or GroupA/All_Variables? or All_Groups/Variable1? etc?
What is considered a statistically significant difference? (0.1, 1, 10, 100, ...?)

**konradz** · 05-16-2013, 01:55 PM

Hi Tigeravatar,

Thanks for your answer. I'm calculating average for the variable 1, 2, etc. Traditionally, I would assume that p < 0.05 is statistically significant.

**tigeravatar** · 05-16-2013, 02:18 PM

konradz,

I'm still a little confused, but I put together the attached example. Is that anything close to what you're looking for?

**konradz** · 05-16-2013, 02:31 PM

Hi,

Thanks for prompt reply. Let's assume that Variable 1 represents percentage of people using the service and the case corresponds to geography. If this statement is true:
For case Case0001, 30% of people are using the service (variable 1) which statistically significantly higher than the average for all the geographies (variable 1) of 5%. I want a cell next to Case0001 to take value 1. I have no problem with pulling the right data using offsets and arrays, that is done. Practically, I want to obtain similar conclusions to those outlined on this page (z-test). Basically, I am not sure how to translate this hypothesis test into formulas. Group is used for browsing, so the end user can browse cases by groups. Average, as well as standard deviation is calculated for the variable.

tigeravatar example for konradz.xls

**ChemistB** · 05-16-2013, 04:55 PM

I think you are looking for the student t test. Assuming this is a two tailed probability in C10 of tiger's sheet and dragged right

=T.TEST(C5:C7,C8:C9,2,2)
I assume this is only an example and you really have a lot more data per group.