Find Max value

[mdileep]

Kindly help me with the following scenarios,
Some values in cell range a1 to a 10 and same as b1 to b10.
I want the max value of cell a1 to a10 but exclude corresponding value in cell b1 to b10 is blank.
A B
12. 15
19 --
18 12
Desire answer 18
Sorry to say but with excel 2007
Thanks.

 

[Fluff]

How about

=AGGREGATE(14,6,A1:A10/(B1:B10<>""),1)

 

[mdileep]

How about

=AGGREGATE(14,6,A1:A10/(B1:B10<>""),1)

Thanks, but aggregate function is not available with excel 2007

 

[Rick Rothstein]

I cannot test this because I do not have your version of Excel, but I think this formula should work but you may have to commit it using CTRL+SHIFT+ENTER and not just Enter by itself (of course, change the ranges to match your actual data locations)...

=MAX(IF(B1:B3="",0,A1:A3))

 

[Fluff]

aggregate function is not available with excel 2007

Oops forgot that.
Rick's formula should work.

 

[mdileep]

I cannot test this because I do not have your version of Excel, but I think this formula should work but you may have to commit it using CTRL+SHIFT+ENTER and not just Enter by itself (of course, change the ranges to match your actual data locations)...

=MAX(IF(B1:B3="",0,A1:A3))

 

[mdileep]

I cannot test this because I do not have your version of Excel, but I think this formula should work but you may have to commit it using CTRL+SHIFT+ENTER and not just Enter by itself (of course, change the ranges to match your actual data locations)...

=MAX(IF(B1:B3="",0,A1:A3))

Tested and found OK, Thanks bro...

 

[mdileep]

Oops forgot that.
Rick's formula should work.

Yes, Thanks.