Tips & Tricks 74 - We have AVERAGEIF. What about MEDIANIF and MODEIF?

By |Saturday, January 17th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

Excel doesn't provide MEDIANIF and MODEIF. You will have to use Array formulas to achieve these functionality. Let's assume that our data is like below -

1

To calculate MEDIANIF and MODEIF, enter below formulasĀ i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=MEDIAN(IF(A2:A13="M",B2:B13))

=MODE(IF(A2:A13="M",B2:B13))

Non-Array alternatives

For MEDIANIF

=AGGREGATE(16,6,(B1:B13)/(A1:A13="m"),50%)

For MODEIF

=INDEX(B1:B20,MATCH(MAX(INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,)),INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,),0))