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.
<tbody>
</tbody>
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.
| A | B | C | D | E | F | G | H |
| Level | Data | Group | Above/Below Median | Level | Median | No. Above Median in Group A | |
| 1 | 23985 | A | 1 | 28261 | FALSE | ||
| 1 | 26333 | A | 2 | 28261 | FALSE | ||
| 1 | 26802 | B | |||||
| 1 | 27300 | B | |||||
| 1 | 29222 | B | 1 | ||||
| 1 | 29409 | A | 1 | ||||
| 1 | 30047 | B | 1 | ||||
| 1 | 30536 | A | 1 | ||||
| 1 | 30537 | B | 1 | ||||
| 2 | 30302 | A | |||||
| 2 | 23878 | B | |||||
| 2 | 30926 | B | |||||
| 2 | 25016 | A | |||||
| 2 | 24478 | B |
<tbody>
</tbody>
