I've always used the Quartile formula for calculation quartiles on a set of numbers. Then someone gave me a quartile formula using a MATCH function. As you can see on the attached document, columns D and E yield different results. I'm not sure how the formulas in column D (using MATCH) result in different answers from Column E. Can someone explain what is going on so I can explain it to others in the office? Is one method preferred over the other. Thanks you for your thoughts and opinions.
Formula Differences
- Thread starter grumpus
- Start date
In cells H7:H10, look carefully at the 2nd formula in your QUARTILE function. You chose to take the 0th (min value), 1, 2, and 4th quartile. This gave numbers of 617, 653, 663, and 692.
The formulas in col D with the MATCH function are grabbing the 1, 2, 3, and 4th quartile which are values of 653, 663, 670, and 692. Since the quartiles are different, that's why you get different results.
To get the MATCH formula to be similar to what you're doing with VLOOKUP table, change formula to:
=5-MATCH(C6,QUARTILE(C$6:C$21,{0,1,2,3}),1)
The formulas in col D with the MATCH function are grabbing the 1, 2, 3, and 4th quartile which are values of 653, 663, 670, and 692. Since the quartiles are different, that's why you get different results.
To get the MATCH formula to be similar to what you're doing with VLOOKUP table, change formula to:
=5-MATCH(C6,QUARTILE(C$6:C$21,{0,1,2,3}),1)