Why "+ 0" instead of VALUE function?

[masteff]

I've noticed in several threads that people give solutions that use "+ 0" to make string of numbers be forced to a number. Why is that better than using the VALUE function? If I use the function, then I know for certain it will make it a number instead of text. Further, by using a function, I make it obvious to future users what is being done whereas "+ 0" is more likely to be seen as unnecessary and therefore be edited out.

Thanks for any insight.

[NBVC]

Mostly preference, lazineness (shorter) maybe more efficient (one less function to evaluate, especially if you have nested functions (only allowed max 7 pre-2007))... there are other ways too... *1, --, etc..

The Concatenate() function is also replaced by using the & between items to link...

[Palmetto]

I don't think there is any inherent advantage to using either approach. I see it as largely a matter of preference with the +0 being "short-hand" for getting the desired result.

Further, IMO, adding the another FUNCTION within a formula, which in turn adds more text and brackets () can appear more confusing than +0. The latter is easily understood as "add zero", though the purpose may not be so apparent.

Why would you give users the privilege of editing your formula if they don't understand it? Seems logical one would ask before taking action to amend a formula whose elements they're not sure about.

[martindwilson]

global warming...uses less key presses.. hence less electricity therefore lower carbon footprint. well it's a theory.

[DonkeyOte]

Going back to the point of efficiency... I don't know if VALUE is more/less efficient than 0+, after all XL is still having to a make "a" calculation... what we do know is that double unary -- is regarded as being slightly quicker method of coercion than 0+, 1* etc... single unary quicker still of course but this inverts the results so not that useful in most circumstance ... regards the double unary, this is one of the reasons why in SUMPRODUCTs you often see the:

SUMPRODUCT(--(x=y),--(y=z),values)

approach used in preference to

SUMPRODUCT((x=y)*(z=a)*values)

(there are other advantages to this approach - but limitations also when compared to the * method)

If we disregard efficiency args and look only at "transparency" I would say masteff and Palmetto both have valid points... that is to say yes I would agree that the purpose of:

VALUE(textstring)

is certainly more blatant to the lay person than

0+textstring

however that said I would certainly err on Palmetto's side in so far as : "if my end users know nothing of coercion do I really want them altering formulae ?"

I would argue most people capable of manipulating even remotely complex formulae either understand coercion or worse case scenario are inquisitive enough to test what 0+ does thereby establishing the purpose of 0+ in the first instance. I would say that 0+ is used often in preference to -- because the latter is even less intuitive.

At the end of the day our personal preferences for functions etc pretty much always dictate our spreadsheet design and unless those preferences are hideously inefficient when compared to alternatives I don't think that's an issue in the slightest. As a general rule in XL there are countless ways to achieve the same goal and understanding which to be THE optimal is in reality a never ending process... I would go so far as to say there are very few who actually know the answer, and by very few I mean a handful (and sadly that does not include any of us)

[Colin Legg]

I've noticed in several threads that people give solutions that use "+ 0" to make string of numbers be forced to a number. Why is that better than using the VALUE function? If I use the function, then I know for certain it will make it a number instead of text. Further, by using a function, I make it obvious to future users what is being done whereas "+ 0" is more likely to be seen as unnecessary and therefore be edited out.

Thanks for any insight.

AFAIK both approaches will produce the same results when converting numbers stored as text to numbers.

There is a difference when converting boolean values though.
=VALUE(FALSE) and =VALUE(TRUE) will both return #VALUE!
=FALSE+0 and =TRUE+0 will return 0 and 1.

Perhaps then, the fact that coercing would be expected to be both slightly more efficient and slightly more versatile (consistency) than VALUE() are reasons that addition, negation etc... are used more frequently?

I agree using VALUE() is more transparent. If a user doesn't know what the function does, a quick look in the helpfile will give ample clarification.

[masteff]

Figured I'd add a few tidbits to the discussion I started....

1) It does occur to me that in the old days of slower processors and less RAM, the argument would have been made that using a basic math operation (+0 or double-minus) would process faster than calling a function.

2) to sum a column of text values, I was able to use sum(--range) and then pressing ctrl-shift-enter to make it an array function. very slick. sure beats having to insert a column, add and copy down a formula to convert the value and then sum it. (sum(value(range)) works as an array function too)

3) my only rebuttal to Palmetto's comment of: "Why would you give users the privilege of editing your formula if they don't understand it?" is that as an accountant with a bunch of basic level spreadsheets that rarely use advanced functions, I'd rather that my successors be able to easily interpret what I was doing. accountants tend to reuse the same spreadsheets for consistency so the risk is high that in the future someone other than myself will have to interpret the guts of the computations.
of course the rebuttal to my rebuttal is that spreadsheets can have comments added, so if in doubt, use the better solution and add a comment to explain what's happening if there doubt about a successor won't understand a formula.

[kotoma]

Well, it since to be there are lots of ways or solutions to...
"=VALUE(FALSE) and =VALUE(TRUE) will both return #VALUE!
=FALSE+0 and =TRUE+0 will return 0 and 1."

I look for a formula that would do that; replace the #VALUE! for 0 or even better a blank cell.
There are still an unsolve thread here:
http://www.excelforum.com/excel-work...lue-error.html

Is there anyone who knows how too?

[martindwilson]

you do not make it clear in other thread have a look

[teylyn]

kotoma, the VALUE() function and the #VALUE! error are two different things. In your thread you're dealing with the Value error. That cannot be changed by adding a zero!

[kotoma]

Sure teylyn,
I'm new to excel; I reading lot in this forum to learn.
I will help others also when the time come with more experience.

I'm very grateful for you suport and all the people in the forum.

Thank you everyone for your help!