×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Formula returns N/A but not valid

Formula returns N/A but not valid

Formula returns N/A but not valid

(OP)
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.

RE: Formula returns N/A but not valid

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

RE: Formula returns N/A but not valid

(OP)
They do show up.

RE: Formula returns N/A but not valid

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close