CODEWITHSUNDEEP
excel
Dipto_Das
Dipto_Das

Reputation: 53

How to calculate median of column values satisfying two conditions in excel?

I have an excel table:

+-------------+--------------+
|   Type (C)  |    value(D)  |
+-------------+--------------+
| AAA         | 0000         |
| BBB         | 12004        |
| AAA         | 5675         |
| BBB         | 0000         |
| BBB         | 7880         |
| AAA         | 9563         |
| AAA         | 878          |
+-------------+--------------+

I want to have the median of the positive (greater than zero) values of type AAA.

I tried as:

=MEDIAN(IF(AND(C$2:C$6950="AAA",D$2:D$6950>0),D$2:D$6950))

It returns zero. The table has many greater than zero values, hence it should be something greater than zero. How to do this query?

Upvotes: 2

Views: 1332

Answers (1)

Rob Anthony
Rob Anthony

Reputation: 1813

You can't use the AND command here, it doesn't work, instead multiply the two boolean sections together. This will achieve the same thing as AND.

=MEDIAN(IF((D$2:D$6950>0)*(C$2:C$6950="AAA"),D$2:D$6950))

Press Ctrl, Shift, Enter to put the formula in. For the data given, I got a median of 5675.

If the 'numbers' in Column D are stored as strings, you need a different version of the formula to cope with the conversion

=MEDIAN(IF((D$2:D$6950<>"0000")*(C$2:C$6950="AAA"),VALUE(D$2:D$6950)))

Upvotes: 3

Related Questions