I'm trying to use an AND operator inside MAX and IF functions and am having trouble. I want to find the maximum value from column C where both are true:
- column A = $D$1
- the max only considers the top 80% of the values in column B
So in the example below, the answer would be 7, since of the rows with column A = "foo" and the top 80% of column B which are 3,4,5,6 the max value is 7.
I have two components working:
=MAX(IF($A$1:$A$12=$D$1,$C$1:$C$12))
returns 12
=MAX(IF(B1:B12>PERCENTILE(B1:B12,0.2),C1:C12))
returns 8
If I put them together like this it returns 0 (even with ctrl+shift+enter):
=MAX(IF(AND($A$1:$A$12=$D$1,B1:B12>PERCENTILE(B1:B12,0.2)),C1:C12))
The solution with both AND clauses should be 7
Can anyone help with this?

Aucun commentaire:
Enregistrer un commentaire