# Thread: Array Formula not working Thanks: 0 Likes: 0

1. ## Array Formula not working

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??  Reply With Quote

2. ## Re: Array Formula not working

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  Reply With Quote

3. ## Re: Array Formula not working

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

4. ## Re: Array Formula not working

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!!  Reply With Quote

5. ## Re: Array Formula not working Originally Posted by 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!!
Both ranges?

You can take that into account in the formula...
Code:
```=MAX(
IF(ISNA(\$A\$3:\$A\$65536),
IF(\$A\$3:\$A\$65536=A4,
\$Q\$3:Q\$65536)))```
Code:
```=MAX(
IF(\$A\$3:\$A\$65536=A4,
IF(ISNUMBER(\$Q\$3:Q\$65536),
\$Q\$3:Q\$65536)))```
Code:
```=MAX(
IF((1-ISNA(\$A\$3:\$A\$65536))*ISNUMBER(\$Q\$3:Q\$65536),
IF(\$A\$3:\$A\$65536=A4,
\$Q\$3:Q\$65536)))```  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•