mercredi 14 février 2018

Excel - Using max, if & and together

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?

enter image description here

Aucun commentaire:

Enregistrer un commentaire