Formula returns N/A but not valid 1

[dmccallum]

My formula looks at a range and if it can't find the range name it returns a 0 (for calculation purposes) otherwise it totals the range. Example:

=+IF(ERROR.TYPE(April_524)=5,0, SUM(April_524))

The formula works for several ranges correctly and for several ranges it returns the #N/A. I compared the ranges that do and do not work and can't see any difference. I have even totaled the ranges and get a valid number. I've copied the formula into all the cells only changing the range number. Excel help says #N/A is returned because the numbers are not there or can't be found but I was able to total.

 

[RickSpr]

Do the range names show up in the Name box when the worksheet holding this formula is active? One thing that threw me for a while was that range names aren't always globally visible across all worksheets. If that could be the problem, try qualifying the range names with the worksheet name, e.g. Sheet5!April_526 Rick Sprague

 

[dmccallum]

They do show up.

 

[RickSpr]

I was able to reproduce your problem. There are two problems here. First, ERROR.TYPE is supposed to be given an error value as its argument. When you use a range name instead, ERROR.TYPE gives you either 3 (equivalent to a #Value! error) or #NA. I typed the same formula into two different cells, and one got #NA and the other got 3. Amazingly, it appears to give you #NA if the formula is on the same row as one of the cells in the range, and 3 otherwise. Very strange!

So the first problem is that when you use ERROR.TYPE with a range name, you get unpredictable results.

Second, ERROR.TYPE is meant to be used only when you already know you have an error value. If the cell contains a valid value, ERROR.TYPE returns #NA. In Excel, any time any part of an expression evaluates to #NA, the whole expression evaluates to #NA. So trying to evaluate #NA=5 doesn't give you either True or False, it gives you #NA, and IF(#NA) also evaluates to #NA.

There are two exceptions to this rule. If #NA is the argument to either ISNA() or ISERROR(), then the expression result will be True or False.

I'm not sure what you intended your formula to do. If you want to substitute 0 when the range name in the formula is undefined, use the following formula:

  =IF(ISERROR(SUM(April_526)),IF(ERROR.TYPE(SUM(April_526))=5,0,SUM(April_526)),SUM(April_526))

Rick Sprague