Array Formula not working

[cmhoz]

Hello...

I'm trying to do a 'MAX IF' array... and I THINK I have the formula right, but it's returning a #N/A

This is my forumla - and I am remembering to CSE to exit it, etc.

=MAX(IF($A$3:$A$65536=A4,$Q$3:Q$65536,FALSE))

It should definately have an answer for the line I'm testing it on. I thought it was because the values in Q are dates, but I changed them to be straight values and it still returned a #N/A.

Have I missed something??

 

[Andrew Fergus]

Hi

Two thoughts. If you know the minimum value will be greater than zero (which a date is) then change the FALSE part to 0. {ie zero}

Secondly (at the risk of stating the obvious - forgive me if you have already checked this) check that both columns A and Q do not contain any errors - in particular a "N/A" error.

Andrew

 

[Aladin Akyurek]

Do you have #N/A in the ranges the formulas refer to?

 

[cmhoz]

Thanks guys!

Sometimes it's useful to have an extra brain or two.

I did, in fact, have a #N/A in my array. I thought I had checked for them before, so I discounted that as a reason - but there it is!


Thanks!!

 

[Aladin Akyurek]

Thanks guys!

Sometimes it's useful to have an extra brain or two.

I did, in fact, have a #N/A in my array. I thought I had checked for them before, so I discounted that as a reason - but there it is!


Thanks!!

Both ranges?

You can take that into account in the formula...

=MAX(
   IF(ISNA($A$3:$A$65536),
   IF($A$3:$A$65536=A4,
     $Q$3:Q$65536)))

=MAX(
   IF($A$3:$A$65536=A4,
   IF(ISNUMBER($Q$3:Q$65536),
     $Q$3:Q$65536)))

=MAX(
   IF((1-ISNA($A$3:$A$65536))*ISNUMBER($Q$3:Q$65536),
   IF($A$3:$A$65536=A4,
     $Q$3:Q$65536)))