Median Test

[srdavisgb]

Greetings:

I am trying to build-up a formula to perform a median test (e.g., chi square) on a set of data. There are a number of conditions that need to be applied when looking up the data that is complicating the formula. Below is a subset of the data in my worksheet. I'm using two tables: the first four columns represent the data in my worksheet and the last two column respresent a summary table. Eventually, I will build out the second table to calculate a chi square statistic on the values in each level.

First Question: In the Median cell I have the following formula: =IF($A$2:$A$14=1,MEDIAN($B$2:$B$14),0). As you can see, I'm getting a result of 28261. However, I would expect a result of 29222 because it is mid-way among all values with a level value equal to 1. Actually, 28621 is the median for all values in the data column, so my formula is not recognizing the condition of level=1. What is the correct formula to calculate the median for the values in the Data column that are equal to level 1?

Second Question: Notice the Median for Level 2 is also incorrect.I would expect a value of 25016. What is the correct formula to calculate the median for the values in the Data column that are equal to level 2?

Third Question: In the Above/Below Median column (column D) I have the following formula: =IF(B2>VLOOKUP(A2,$f$3:$g$11,2),1,0). In the third column (column H) of the second table (No. Above Median in Group A) I have the following formula: =countif(vlookup(F2,A2:D16,4)=1,0). Obviously, this formula is not working in column H. How do I get the number of Group A's in Level 1 that have Above/Below Median of 1? Basically, I want to know how many subjects in Level 1, Group A have a score above the Median.

Thanks, in advance, for your assistance.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]Level
[/TD]
[TD]Data
[/TD]
[TD]Group
[/TD]
[TD]Above/Below Median
[/TD]
[TD][/TD]
[TD]Level
[/TD]
[TD]Median
[/TD]
[TD]No. Above Median in Group A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]23985
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]28261
[/TD]
[TD]FALSE
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]26333
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]28261
[/TD]
[TD]FALSE
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]26802
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]27300
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]29222
[/TD]
[TD]B
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]29409
[/TD]
[TD]A
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]30047
[/TD]
[TD]B
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]30536
[/TD]
[TD]A
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]30537
[/TD]
[TD]B
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]30302
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]23878
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]30926
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]25016
[/TD]
[TD]A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]24478
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

1. =MEDIAN(IF($A$2:$A$14=1, $B$2:$B$14))

The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.