Can I use a Named Range as an Argument for the N Function in Excel?

In Excel 2013, if I use a named range as the argument for the N function in a formula, the result is not what I expect.

For example, if I name a range (say, $A$2:$A$11 as "Volume"), and fill another range (say, $B$2:$B$11) with the formula"=N(Volume)", I would expect cells in Column B to show the result of the N function for their adjacent cells in Column A.

Instead, *all* cells in Column B show the result of the N function for only the *first* cell in Column A (i.e. $A$2).

Some functions in Excel appear to work as I expect when I use named ranges as their arguments, but the N function (and perhaps others?) do not.

Do I misunderstand the way Excel uses named ranges; is my formula syntax incorrect for the results I expect; is the N function not returning valid results; or is there some other explanation?
billparsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Keyboard CowboyCommented:
Put a plus in Front of the range name. That will force the proper result.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billparsAuthor Commented:
Thanks, Cowboy.

The leading "+" syntax appears to work (i.e. "=N(+Volume)").

Can you explain why?

What does a leading "+" mean in Excel (other than "add")?

Is it documented, or merely a hack?
0
Rob HensonFinance AnalystCommented:
The N function is designed to convert a single cell to a numeric value where possible so would not normally expect a range.

For your sample, in B2 use "=N(A2)" and then copy down as far as required; double click the bottom right corner of cell B2 and it will fill down as far as required for adjacent data in column A.

Can I ask why you are using the N function anyway? Looking at the Online help for the function it is generally not needed.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

billparsAuthor Commented:
Mr. Henson,

Thanks for responding.

The N function really is just a canary in the coal mine.  My real concern is with Excel functions -- in general -- returning accurate results for named ranges.

For many years, a client has downloaded and distributed a fill-in-the-blank workbook, to collect data from hundreds of vendors annually.  These workbooks are pre-populated, with each row representing a single property (sometimes thousands of properties per vendor).  The vendors fill in a handful of empty columns per row, and return their workbook for upload.

Several of the columns in each row are read-only fields that calculate values, based on user input.  All of the columns are named ranges, and the calculated fields contain formulae that reference them (e.g. a simple Variance column contains the formula "=IF(N(Previous Volume) = 0, "-", (Current Volume – Previous Volume) / Previous Volume)").

As you can see, the N function is used here only to avoid an error from an invalid "Previous Volume" entry (blank, zero, or non-numeric).  Many of the vendors are unsophisticated mom-and-pop shops, without IT departments, and using named ranges helps "self-document" the process for them.

In the past, Excel functions we used returned accurate results for named ranges, but as we add more sophistication to formulae in these workbooks, we are discovering unanticipated issues (e.g. the N function result for named ranges).

Keyboard Cowboy gave a one-line answer that seems to work (at least in this simple context).  But he or she has not responded to my inquiry of *why* it works.  Can you explain this?  Is it a hack, or does Microsoft document leading "+" characters for named ranges as function arguments?

I would appreciate ANY insight you could offer on this latter line of inquiry.

Thanks.
0
Rob HensonFinance AnalystCommented:
I am not aware of the +addition or how it works, first time I have seen it.

If you are looking for ease of reading in the formulas, look at use of a Table rather than a standard list. When a range is converted to a table, formulas that reference fields within the table use the column header rather than the column/row reference. In addition as the table gains new data the formulas automatically adjust to still refer to the table whereas using a named range you may have to update the range settings for the named range.
0
hnasrCommented:
Microsoft documentation indicates that N function is for compatibility with other spread sheets.

N(definedName) yields the first value in the column range.
N(+definedName) desplays the corresponding value in cell in same raw.
You may use N(definedName+0)

I guess it directs Excel to pass through the range and display the corresponding value in cell in the same raw.
I could not find more helpful documentation.

If you need to negate the values, then use N(-definedName)
1
Rob HensonFinance AnalystCommented:
Also, for avoiding errors, you can use the IFERROR function, with your example:

=IFERROR(Current Volume – Previous Volume) / Previous Volume,"Check Entry")

This checks if the result of the formula gives an error, if so it will show the "Check Entry" message otherwise it shows the result of the formula. The "Check Entry" can obviously be amended to what you want.
0
hnasrCommented:
Keyboard Cowboy got that, was it trial and error or it is documented somewhere?
1
Keyboard CowboyCommented:
I don't' really have an answer on where I learned that.  I've been doing excel for a long time and just know that you must use "+"  in front of ranges when using in a formula sometimes as in this case.  Unsure if it's documented anywhere officially by MS but then a lot of tricks - I mean techniques - that are used aren't documented by MS.  I just google things a lot of find this kind of stuff.
Cheers
1
billparsAuthor Commented:
hnasr,

Thanks for posting the following:

"Microsoft documentation indicates that N function is for compatibility with other spread sheets.

N(definedName) yields the first value in the column range.
N(+definedName) desplays the corresponding value in cell in same r[o]w.
You may use N(definedName+0)"

Do you have a Microsoft link for that, or is that just your own understanding?

None of the documentation I can find seems to address why "=Volume" returns the "Volume" value from the same row, but "=N(Volume)" returns the "Volume" value from the first row.  My fear is that other Excel functions, beyond the N function, also will return unanticipated results (under critical and unexpected circumstances).

Thanks.
0
billparsAuthor Commented:
Cowboy,

Thanks for the follow up.

You posted that:

I just google things a lot [and] find this kind of stuff.

I extensively searched the net (with every combination of terms I could think of) before resorting to EE, and also after your initial reply.

I could not find anything about using a leading "+" sign with named ranges, either as arguments for Excel functions or in any other context.

Do you have any links for what you found regarding this?

Thanks.
0
billparsAuthor Commented:
Thanks, hnasr, I already saw that link, but I could not find anything about either the "+" sign or named ranges.  Am I simply overlooking it?

Also, the only online content I found that even comes close to the issue is this link, which addresses only array formulae:

https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/

(Scroll down to the section titled, "AND and OR operators in Excel array formulas".)
0
hnasrCommented:
So many forums are there discussing office issues.
They normally discus issues that are not documented.

So +definedName you need to accept it as a work around, that is not documented, or at least I did not find when I searched. From now on a user can find this thread as a document for +defindName.

The same applies to definedName + 0.
0
Keyboard CowboyCommented:
Yeah - a lot of things are no documented  - which is why they pay me to figure things out  -
0
billparsAuthor Commented:
I suspect this has to do with Excel recognizing the + sign as a logical OR operator, and somehow coercing the N function to recognize a corresponding array-element counterpart in its named-range argument.

As with everything else that *seems* to work with Microsoft products, but which is utterly devoid of documentation, it may or may not work in any given circumstance.

I also fear the same counter-intuitive result with other functions, beside the N function -- and when I least expect it.

Even though I am marking this question as "closed", it still seems "open" in a larger sense.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.