Dynamically Named Range and IsError()

[Stephen Lloyd]

Greetings! Thanks for any help! Before starting, I don't necessarily need a
treatment, I'm really hoping for a diagnosis.

I have a range defined as the following:

=OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1)

It expands each time a payment is entered and column E is the amount field.
Everything is fine to this point.

On a summary page there is a cell totalling the payments, which my
application checks. The formula is:

=IF(ISERROR(rAmount),0,SUM(rAmount))

Everything is still fine... Unless the user enters exactly 2 or 3 payments
throughout the day. One payment works, four payments and up works, but two
or three payments causes the named range to return an error.

Does anyone have any idea what might cause this? For the record, no error
is returned if the formula is changed to

=IF(ISERROR(SUM(rAmount)),0,SUM(rAmount))

Any help is appreciated. Thanks in advance!

 

[JP]

What is rAmount referring to?

--JP

 

[Stephen Lloyd]

sorry, rAmount is defined as the dynamic named range
=OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1)

Column E is the amount field. Amounts are generally in the 100's or
thousands. I checked and all the entries are fine (valid numbers). If I
artificially add or take out entries so that there is either only one entry
or four or more entries the conditional formula
=IF(ISERROR(rAmount),0,SUM(rAmount)) works great. But when there are either
two or three entries it is broken and returns 0.

I appreciate the followup question.

 

[T. Valko]

=IF(ISERROR(rAmount),0,SUM(rAmount))

rAmount is a range of cells therefore the formula would have to be array
entered. Also, it depends on where the formula is entered as to how it
"acts". There is a rule called the implicit intersection which applies to
array formulas. If an array formula is not entered as an array it will only
evaluate references which are on the same row/column that the formula is
entered on.

However, that formula as written is not logically correct. You'd want to use
this formula array entered** :

=IF(OR(ISERROR(rAmount)),0,SUM(rAmount))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

=IF(ISERROR(SUM(rAmount)),0,SUM(rAmount))

That formula is using the SUM function so that eliminates the need to array
enter.

Another way that will ignore any errors:

=SUMIF(rAmount,"<1E100")

 

[Aviashn]

Biff,

Thanks for your reply.

Ok, I'll buy that. Especially since, my total (=IF(Iserror(.....)) is
on row 5 of worksheet 1 and it works when the records reach row 5 of
worksheet two.

However, it still works when there is only one record. Is this
because the range is only a single cell and not an array?

 

[T. Valko]

When the dynamic range rAmount is just a single and if that single cell is
the first cell of the referenced range then the non-array entered array
formula would work.

--
Biff
Microsoft Excel MVP


Biff,

Thanks for your reply.

Ok, I'll buy that. Especially since, my total (=IF(Iserror(.....)) is
on row 5 of worksheet 1 and it works when the records reach row 5 of
worksheet two.

However, it still works when there is only one record. Is this
because the range is only a single cell and not an array?