Get Maximum value of column B, given condition in Column A

[mgt]

Hello,

Column A ranges from 0 to 1 for 1143 rows
Column B can be a negative to a positive number

Column A Column B
0.1
0.2
0.399 5800
0.41 8065
0.6
1.0

I can get the maximum valuue in Column B =MAX(B2:B1143) = 8065
I can get the address of that cell =ADDRESS(MATCH(MAX(B2:B1143),$B$2:B1143,0),1)

But I want only the maximum value if the corresponding value in A is <= .40, so from the example above, I want the value of 5800 returned not 8065.
Then which cell has this maximum value.

Many thanks,


Margaret

 

[jasonb75]

Try

=MAX(IF(A2:A1143<=0.40,B2:B1143))

Confirmed as array formula with Shift Ctrl Enter

Or if Column A is sorted ascending

=MAX(B2:INDEX(B2:B1143,MATCH(0.4,A2:A1143)))

 

[mgt]

Thanks Jason.

When I try
=MAX(IF(A2:A1143<=0.40,B2:B1143))
I get the maximum value in column B of 8070 but A for that cell is .9895 rather than a value where a is <=0.4

*BUT* I get the correct value as I have A sorted when I use
=MAX(B2:INDEX(B2:B1143,MATCH(0.4,A2:A1143)))


Thanks

Margaret

 

[jasonb75]

When I try
=MAX(IF(A2:A1143<=0.40,B2:B1143))
I get the maximum value in column B of 8070 but A for that cell is .9895 rather than a value where a is <=0.4

That would suggest you didn't array confirm the formula.

You need to press Shift Ctrl Enter to close the cell after you enter the formula, not just Enter.

 

[mgt]

That would suggest you didn't array confirm the formula.

You need to press Shift Ctrl Enter to close the cell after you enter the formula, not just Enter.

Thanks for that



Margaret