Array Formula and #N/As ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 13 Jul 2007 14:45 GMT

Hi,

I'm using the following formula but it's not working because my SUM
range E29:E59 contains #N/As. I know one solution is to add an IF
statement to the formulas in E29:E59 to return "" if not avialable but
I'm trying to find a olution that works *with* the NAs is the range.

=SUMPRODUCT(--(B29:B59>=B94),--(B29:B59<=C94),(E29:E59))

In any other array formula I'd try use IF and ISNUMBER but am not sure
if this is possible with Sumproduct.

Can anyone advise how I can amend this formula so that it works with
#N/As in E29:E59. Or, another forumla that does the same thing?

Many thanks,

Jason

Reply to this Message

Bob Phillips - 13 Jul 2007 15:17 GMT

=SUM(IF(ISNUMBER(E29:E59),IF((B29:B59>=B94)*(B29:B59<=C94),E29:E59)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi,
>
[quoted text clipped - 14 lines]
>
> Jason

Reply to this Message

Jay - 13 Jul 2007 15:53 GMT

> =SUM(IF(ISNUMBER(E29:E59),IF((B29:B59>=B94)*(B29:B59<=C94),E29:E59)))
>
[quoted text clipped - 3 lines]
> not try to do this manually.
> When editing the formula, it must again be array-entered.

Thanks Bob,

I'd figured a similar solution out, except I had the ISNUMBER check in
the same IF as the other two conditions and had an ,0 ELSE condition.

Can SUMPRODUCT not accomodate an ISNUMBER check in the same way?

Cheers,

Jason

Reply to this Message

Bob Phillips - 13 Jul 2007 18:15 GMT

The problem is that with SP, even adding an ISNUMBER check doesn't solve it
because it still evaluates the NA's in the range. It is not linear, whereas
with the IFs you force a linear evaluation. You can't add IFs to SP as that
will change the range sizes and return a #VALUE error.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> =SUM(IF(ISNUMBER(E29:E59),IF((B29:B59>=B94)*(B29:B59<=C94),E29:E59)))
>>
[quoted text clipped - 14 lines]
>
> Jason

Reply to this Message

Jay - 14 Jul 2007 16:11 GMT

> The problem is that with SP, even adding an ISNUMBER check doesn't solve it
> because it still evaluates the NA's in the range. It is not linear, whereas
> with the IFs you force a linear evaluation. You can't add IFs to SP as that
> will change the range sizes and return a #VALUE error.

Thanks for taking the time to explain it. That's been a real help in my
understanding of SP.

Regards....Jason

Reply to this Message