Text to Number Conversion - Which functions should be used?

[aaalviny]

Disclaimer:
I am not turning a deaf ear to others' comment, but I just had enough evidence to turn them down. I don't turn them down due to no reasons.


p.s. I do admire the use of converting 8-digit number-formatted dates to a date format by use of TEXT("0000-00-00")
I think it's the best solution given the Excel's behaviour on dates.
I didn't thoroughly use it although I heard it once before, and this time it reminds me again.

just an example which I learnt here.


and p.s. I can't find anywhere suitable to put this article, so I probably put it in this board






Text to Number Conversion - Which functions should be used?


sometimes we encounter situation that we may need to convert texts to numbers,
by using either VALUE(A1), --A1, A1*1, A1^1, A1+0, INT(A1)
which of them should be used? Below are my analysis


in the context, --A1, A1*1, A1^1, A1+0 and INT are of the same type, and represented by --A1 in the discussion below, unless otherwise specified.


1. -- or INT() or VALUE() matches the aim of the formula?


the aim of these are to convert texts to numbers
the underlying meaning of --, is to add twice the negative signs to A1, which does not show the meaning of conversion.
the aim of INT() is to extract the integer portion of a number
on the other hand, VALUE(), the function itself, means to convert the texts to number, which matches the true aim




2. -- is simpler than VALUE()?


By simple, it doesn't mean the length, but it means the ease of understanding, and edit/update
VALUE() is easier to be understood than --, because
as mentioned before, VALUE() matches the aim to convert texts to numbers
If one writes VALUE(A1), I think every excel users, even Newbie, can understand the meaning of the formula very well
On the other hand, there are always users asking what -- is.




3. Convention is to use --?


Convention doesn't necessarily mean the correct method.
And everywhere is using different conventions for this conversion (My environment uses *1)
There are so many kinds of conventions to -- because it does not represent the original aim
e.g. If one wants to use a cell to store A1 + 2
=A1+2*1 won't be used, =A1+0+2 won't be used, =(A1+2)*1 won't be used.
because the correct representation is =A1 + 2
although all gives the correct result of the value.


and "convention" is to use VLOOKUP but not INDEX+MATCH
in fact INDEX+MATCH is better in various aspects, except the length being longer
(this is another issue I can talk about if you wish)






4. VALUE() requires one more parenthesis than --


Yes it's true, you may argue excel 2003 (that's what I am using) only allows 7 in one cell.
The fact is, under a good excel design, 3 parenthesis in the formula of any cells are already too much.
too many parenthesis also mean harder to read and maintain
There may be violation of DRY principle or the user is putting too much things in one cell.
some examples of wrong usage includes:

=IF(Curr = "USD", a, IF(Curr = "GBP", b, IF(Curr = "JPY", c, IF(Curr = "CAD", d))))

=IF(ISNA(MATCH(1, $A$1:$A$3, 0)), 0, MATCH(1, $A$1:$A$3, 0))

=VLOOKUP($A1, $B$2:$F$10, 2, 0)*VLOOKUP($A1, $B$2:$F$10, 3, 0)+VLOOKUP($A1, $B$2:$F$10, 4, 0)-VLOOKUP($A1, $B$2:$F$10, 5, 0)+VLOOKUP($A1, $B$2:$F$10, 4, 0)/VLOOKUP($A1, $B$2:$F$10, 5, 0)

and just a random search on the threads:

=LOOKUP(9.99999999999999E+307,MID(SUBSTITUTE(A2," ","x"),SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),ROW(INDIRECT("1:"&LEN(A2)))),ROW(INDIRECT("1:"&LEN(A2)))),5)+0)

by immediatelly looking the formula, do you find it trival to understand the formula?
personally I don't
If you find it trival, then you may use it, and I admit that you are really really very smart!!
(if you can be certain that only you will read the file in the future -- unless you delete it, how can you sure the file won't be exposed to others?)






5. -- is faster than VALUE?


Yes, -- is faster than VALUE
by my machine, converting 3,000,000 texts to numbers requires
2.44s by --, and
3.08s by VALUE
with a difference of 0.64s


but there are several points I'd like to make on this speed
a.
it's very seldom to have such large amounts of numbers stored wrongly as texts
I use "wrongly" because wrong data structure was used, e.g. in database, numbers should not be stored in the field type text
If there is really such a large amount of mis-formated texts, there is a problem with the raw data and it ought to be solved.


b.
even 3,000,000 texts give 0.64s difference.
In reality there won't be such large amount of texts.
Moreover, given there is no volatile funtions in the workbook (this is again another issue I can talk about if you wish)
the difference only happens once, and unless the data changes.
yet by proportion, 10,000 texts only give 0.002s difference


This is very micro-optimisation
In view of the small calculation time difference, I think this part is not a matter in comparing -- and VALUE


To supplement, the main calculation speed for excel depends on the number of lookups used and the amount of volatile functions used
which very much relies on the proper data structure, and formula designing e.g. DRY (Don't Repeat Yourself)
It can gives seconds to minutes of difference.
This is the real things about calculation speed that excel designers should care about.
And to have a fast calculation speed, a good workbook design with correct choice of algorithms will mostly do the job.


And volatile functions should be avoided (If it's really required due to poor data structures, copy and paste as value after adding a remark stating the original formula used)
(this is again another issue I can talk about if you wish)


c.
If SUMPRODUCT is used, the speed is slower because of SUMPRODUCT itself, not about -- / VALUE
because SUMPRODUCT should also be avoided and there are other alternatives
(this is again another issue I can talk about if you wish)






6. -- is shorter in terms of length than VALUE()?


Yes, 2 characters comparing to 7 charaters.
Length is the matter of syntax pre-defined by Excel, which is independent of the logic.
Other matters above are more important than the syntax pre-defined.




Conclusion
As a conclusion, please use VALUE() instead of --, *1, ^1, +0 or INT() for Excel best practice






Alvin - 2013 05 18

 

[Jon von der Heyden]

and you cannot beat my aim-matching and being indiscriminate on the other hand, right?

Wrong! I have already made a clear point that VALUE() does not sufficiently support type matching (because it is not exclusive) and that one should test the type using different means. And I have described that with -- coercion is aim-matching because the coercion process is by-design and thus aim-matching! But it seems you fail to comprehend...

P.S. 761 views and still no-one agrees with you. Will you EVER concede??

 

[MARK858]

I don't know whether people are considering the first two advantages "aim-matching+easy understanding" and "indiscrimination" as important when thinking about the best practice
or simply weigh fast and efficient in the top priority. That's what I would like to say here.
Experiencing some changes+shocks and starting to think in a different prospective are a good sign to all, including me.

The only and last thing I will say (on any of the subjects (it seems to move once any subject is discussed)) is the statement above a contradiction of the statement (below) in a previous thread?(which being honest the post I found a lot more irritating than anything in this thread, which is why I didn't post at the time)?

note the following if you want a fast excel (I believe most people here don't care about it)

is there a retraction in order or is this just yet another contradictory statement?

 

[Macropod]

aaalviny: Try to foist your highly subjective and highly opinionated approach in most places I'm familiar with and it's you who would be out of a job. Indeed, an employer who allows you to discriminate against those who don't conform to your ill-conceived standards would likely end up with you and your employer in court. All you've done in this thread is to prove beyond doubt your stupid refusal to accept that you are wrong.

There is a good reason why no-one here supports your stance. You are so far out of step with 'best practice' it isn't funny. Get the message or find a career better suited to your intransigence ...

 

[SydneyGeek]

note the following if you want a fast excel (I believe most people here don't care about it)

Hang on. You use mega-workbooks, and claim no-one is interested in a fast Excel? Do you drink a lot of tea or coffee waiting for your models to recalc? Most of the people I work with want to get the job done. That means fast.

Denis

 

[aaalviny]

Wrong! I have already made a clear point that VALUE() does not sufficiently support type matching (because it is not exclusive) and that one should test the type using different means. And I have described that with -- coercion is aim-matching because the coercion process is by-design and thus aim-matching! But it seems you fail to comprehend...

P.S. 761 views and still no-one agrees with you. Will you EVER concede??

1. indiscrimination:
as I said, it is not sufficient, but at least more sufficient.
Isn't it enough in the decision to choose over two functions? (just an MC with two choices, you want to do an operation as required, and there is different parameter input restrictions between them, which one you will choose? of course the one that matches better and fit, but the looser one!)
we are just not discussing whether a third thing should be used,
and we are here only to compare the two functions on the aim of text -> number conversion


2. aim-matching
I thought you understood it but I think I have to explain it in detail now
please don't use the word "design" to take it for granted and neglect the problems within the design




process of design of functions (or operation):


1. figure out the need of a function, and define the aim
2. choose the choice of name of the function, to comply with the aim above
3. design the algorithm to achieve the aim
4. implementation
5. check whether the final result matches with aim 1
6. check if there are any side effects


for a negative sign:
1. to do the arithmetic negative to a number
2. "-" is a well known mathematical operator to represent this
3. (I don't know the detailed of it)
4. e.g. -3
5. yes it matches
6. -"3" = -3, this conversion from text to value is not the aim of "-" function! arithmetic negative sign is only working on number (but should not do the conversion as well, as the name "arithmetic sign" suggested
If it's the original aim, it should be of a better naming, but not just "-"
i.e. VALUE() <- which its name matches the aim of format conversion


here, double negation is used, but the usage of negative sign is totally thrown away, since the aim to do an arithmetic negative clearly does not apply to the situation here (you only want to convert the text to values),
By only focusing on the side effect of negative sign but do not use the actual aim of negative sign,
it is clearly a non-aim-matching point


and, your "aim-matching" is just your own aim and your result (yes it's clearly matched)
but the aim of the function / operator is clearly not considered!

 

[aaalviny]

The only and last thing I will say (on any of the subjects (it seems to move once any subject is discussed)) is the statement above a contradiction of the statement (below) in a previous thread?(which being honest the post I found a lot more irritating than anything in this thread, which is why I didn't post at the time)?



is there a retraction in order or is this just yet another contradictory statement?

To reply this post I think I better re-illustrate the point of "premature optimization" in anti-pattern

Original whole texts

one of the anti-patterns:
Premature optimization: Coding early-on for perceived efficiency, sacrificing good design, maintainability, and sometimes even real-world efficiency

"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil"


"Premature optimization" is a phrase used to describe a situation where a programmer lets performance considerations affect the design of a piece of code. This can result in a design that is not as clean as it could have been or code that is incorrect, because the code is complicated by the optimization and the programmer is distracted by optimizing.
When deciding whether to optimize a specific part of the program, Amdahl's Law should always be considered: the impact on the overall program depends very much on how much time is actually spent in that specific part, which is not always clear from looking at the code without aperformance analysis.
A better approach is therefore to design first, code from the design and then profile/benchmark the resulting code to see which parts should be optimized. A simple and elegant design is often easier to optimize at this stage, and profiling may reveal unexpected performance problems that would not have been addressed by premature optimization.
In practice, it is often necessary to keep performance goals in mind when first designing software, but the programmer balances the goals of design and optimization.

To reply this post I think I better re-illustrate the point of "premature optimization" in anti-pattern

I am not sure where you found my last statement
note the following if you want a fast excel (I believe most people here don't care about it)

But anyway, there are two regards about the speed, one is those mentioned in the quoted article before, listed again here and more details:
Program optimization - Wikipedia, the free encyclopedia
this is what I correspond to the point that -- is faster than VALUE
As what I mentioned before about aim-matching

Optimization can reduce readability and add code that is used only to improve the performance. (-- is the case over VALUE, only to improve performance)
"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil" (How little speed improvement even for 3 million conversions)
This can result in a design that is not as clean as it could have been or code that is incorrect (aim-matching is sacrificed)
A better approach is therefore to design first, code from the design and then profile/benchmark the resulting code to see which parts should be optimized. (Code from the design first, to change text to values, the aim-matching function is "VALUE()", then, check the benchmark to see which part should be optimized and how much it can be optimized. Again, the main calculation speed barrior should not be taken by this straight forward text-to-value conversion, but more to table structure design, lookups and checking equality and whether these are duplicated and redundant)

so as above, to your question about what I said I cared for the speed,
I care about the latter part in the quote above:
"table structure design, lookups, checking equality and whether these are duplicated and redundant (DRY)"
and, these above, is just the key element in the speed of calculation

In short, it's totally two different prospective of speed. and please don't mix it up!

 

[aaalviny]

aaalviny: Try to foist your highly subjective and highly opinionated approach in most places I'm familiar with and it's you who would be out of a job. Indeed, an employer who allows you to discriminate against those who don't conform to your ill-conceived standards would likely end up with you and your employer in court. All you've done in this thread is to prove beyond doubt your stupid refusal to accept that you are wrong.

There is a good reason why no-one here supports your stance. You are so far out of step with 'best practice' it isn't funny. Get the message or find a career better suited to your intransigence ...

Out of my expectation, I still find people are not thoroughly understanding the most important advantages I stated.
So I explained in further detail again, just the posts above.
If you think it's subjective, feel free to fight back. No one successfully fought back on those points yet.

And, please don't criticize my skills. My skills are well-recognized in my department, even though other colleagues are all advanced excel users who are very familiar with the functions and keyboard shortcuts. And I have already been conducting skills sharing sessions to my colleagues in MS Word and MS Excel and VBA.
for Excel, contents are about calculation flow design, table structure design, volatile functions avoidance, "DRY", benefits of auto calculation mode (illustrated best with no volatile functions), avoid isolated blocks, layout-logic dependency problem, etc.

for the final point, it's because very few people in the professional programming world are dealing with Excel exhaustively. So quite a number of best practice are not identified in Excel and very few people do mention them.
(I am not the first person to state the aim-matching problem of -- with VALUE())

 

[aaalviny]

Hang on. You use mega-workbooks, and claim no-one is interested in a fast Excel? Do you drink a lot of tea or coffee waiting for your models to recalc? Most of the people I work with want to get the job done. That means fast.

Denis
[/COLOR]

First of all,
Mega workbook has no necessary relationship to speed.
If an excel contains many required data and complex calculation, the file size has to be big
Even if a workbook has small file size, there can be many volatile functions / duplicated calculations / array formulas which lead to slow worksheets.

then, when proper design are used (as I always mentioned, proper database structure, proper use of binary lookups, "DRY", no volatile functions, etc.)
the auto calc mode actually is working very well, and everytime I update the cells, only those depending on them are updated. So when I update some far away cells, no calculation is needed.)
at least, for my 200MB well-designed and the historic 80MB not well-designed worksheets, the 200MB one is faster than the 80MB.

I don't need to re-calc in my excel design, all in auto-calculation mode and there's no need to wait while I am working.
I don't need to press F9 when working, for your interest.

 

[Jon von der Heyden]

As of my writing this, this thread has been viewed 896 times and has received 55 replies. And despite all this exposure your notion has not received a single like and not single peep of support. If you're right then we must all be very {fill_in_the_blank}.

1. indiscrimination:
as I said, it is not sufficient

Well that;s it then. If it's not sufficient then there is no such thing as more sufficient, since an explicit data type test will be required anyway. One would be explicit in programming so why not with respect to formula creation? Use of an insufficient function such as VALUE() only increases the risk. So if it's not sufficient, and if data type is important and thus we must discriminate by using more explicit means then of course double negation is a fine choice!

2. aim-matching
1. figure out the need of a function, and define the aim

Ok so now you're suggesting that a function will always be the best match for the need. Rubbish! It is you who said we must look to humane logic and look beyond Excel. Well ok then, lets do that!

So what does VALUE() mean outside of the Excel world?

val·ue [val-yoo] Show IPA noun, verb, val·ued, val·u·ing.
noun
1. relative worth, merit, or importance: the value of a college education; the value of a queen in chess.
2. monetary or material worth, as in commerce or trade: This piece of land has greatly increased in value.
3. the worth of something in terms of the amount of other things for which it can be exchanged or in terms of some medium of exchange.
4. equivalent worth or return in money, material, services, etc.: to give value for value received.
5. estimated or assigned worth; valuation: a painting with a current value of $500,000.

Oh boy, nothing in there about coercion!

In fact if you have and programming experience you will know that it could mean ANYTHING!I mean what the algorithm is, what the function does, is to the discretion of the author! I can think of dozen of other possible things that function, as a word, could support. Can't you??

Ok so what about double negation? So as you so rightly pointed out double negation negates a number (e.g. negate 1 = -1); and then negates it again (negate -1 =1). So any human knows that the only type of information that double negation can yield is a number! It's a lot more logical than VALUE(), isn't it? You've argued the point about boolean logic, and that True cannot really be 1, and thatFalse cannot really be 0. Well I teach Excel and I teach the uses of coercion in formulae. I always ask my audience to pick a number to represent True and to pick another to represent False. I can safely say that EVERYONE has always picked True=1; False=0. And I have asked A LOT of people!

But this IS Excel so lets puts our Excel hats back on. We know that any arithmetic expression must yield a numeric result. We know therefore Excel must attempt to interpret each operand as a numeric result. We know that if it cannot resolve the operand it will yield a #VALUE! error, a very specific error that is aim-matching! We know that double negation is quicker. We know that it is indiscriminate; and that as VALUE() is only partially discriminate we must resort to a more explicit type test. We know that everyone around the world, using EXCEL is using this method.

Alvin I'm going to put it very bluntly! You do not possess the requisite experience nor knowledge of this topic to even begin suggesting changes to conventions. These suggestions could adversely affect people, after all we many many millions of Excel users around the world with access to the internet. On this matter you are wrong wrong wrong! Our conventions have long been around. They have been challenged, debated and ratified! Yes there are some that could do with improvement; but unless you open yourself up to the notion of LEARNING and spend some quality time on the forums (months if not years) then you will NEVER possess the requisite skills and knowledge to accurately challenge these. LEARN the stuff buddy and then come back with a proper notion that might actually improve the way we use Excel.

 

[aaalviny]

As of my writing this, this thread has been viewed 896 times and has received 55 replies. And despite all this exposure your notion has not received a single like and not single peep of support. If you're right then we must all be very {fill_in_the_blank}.


Well that;s it then. If it's not sufficient then there is no such thing as more sufficient, since an explicit data type test will be required anyway. One would be explicit in programming so why not with respect to formula creation? Use of an insufficient function such as VALUE() only increases the risk. So if it's not sufficient, and if data type is important and thus we must discriminate by using more explicit means then of course double negation is a fine choice!

both are not sufficient, but there is one which matches better and narrows down the scope better. That's it.
again, just a comparison of the two functions and their scope, please don't use the third way to intervene and say that the third way is the best and these two are just the same worse. It is out of logic when comparing which one of the two is better.

Ok so now you're suggesting that a function will always be the best match for the need. Rubbish! It is you who said we must look to humane logic and look beyond Excel. Well ok then, lets do that!

So what does VALUE() mean outside of the Excel world?

Oh boy, nothing in there about coercion!

I have to say, function names are not exactly English, yet they have their names giving clues to what the function is about:
and I believe you are checking the dictionary here:
Value | Define Value at Dictionary.com
please scroll down to "Collins" and locate below:

[TABLE="width: 100%"]
<tbody>[TR="class: tr3"]
[TD="class: td3n1, width: 1%, align: right"]6.[/TD]
[TD="class: td3n2"]maths[/TD]
[/TR]
[TR="class: tr3"]
[TD="class: td3n1, width: 1%, align: right"][/TD]
[TD="class: td3n2"]a. a particular magnitude, number, or amount: the value of the variable was 7[/TD]
[/TR]
[TR="class: tr3"]
[TD="class: td3n1, width: 1%, align: right"][/TD]
[TD="class: td3n2"]b. the particular quantity that is the result of applying a function or operation for some given argument: the value of the function for x=3 was 9
[/TD]
[/TR]
</tbody>[/TABLE]

it means to change the parameter inside the brackets to a value!

Or you can say it's not describing the exact clue? but it is not English sentence and "VALUE", by the definition above, should be already enough to identify the aim.
If you say by this definition VALUE cannot represent its aim, then I can nearly say: no function names can be used to represent its aim, "INDEX", "OFFSET", "VLOOKUP", "N", etc. all don't mean the true thing

In fact if you have and programming experience you will know that it could mean ANYTHING!I mean what the algorithm is, what the function does, is to the discretion of the author! I can think of dozen of other possible things that function, as a word, could support. Can't you??

at least I won the preliminary selection contest in programming and was in the about-30-people regular training for grabbing the chance to join International Olympiad in Informatics.
Algorithm can mean anything, but many matters are concerned when designing.
Speed: whether the algorithm is O(1), O, O(log N), etc.
perfectness: whether the algorithm can deal with all special cases
Memory consumption
Readability and Maintenance ease
and also whether it gives side effect or unwanted outputs,
also about "DRY" violation, (yes this principle is taken from programming, not excel)
etc.
It can be anything but there are comparisons!
just like here, there's difference between -- and VALUE
and of course, you have freedom if you are working solely on your own without considering others.
BUT, here definitely not!
[/QUOTE]

Ok so what about double negation? So as you so rightly pointed out double negation negates a number (e.g. negate 1 = -1); and then negates it again (negate -1 =1). So any human knows that the only type of information that double negation can yield is a number! It's a lot more logical than VALUE(), isn't it? You've argued the point about boolean logic, and that True cannot really be 1, and thatFalse cannot really be 0. Well I teach Excel and I teach the uses of coercion in formulae. I always ask my audience to pick a number to represent True and to pick another to represent False. I can safely say that EVERYONE has always picked True=1; False=0. And I have asked A LOT of people!

you are only concerning the output AGAIN!
of course double negation can only produce numbers as understood by all
BUT, no one knows even texts can be processed with arithmetic!

There is a wrong assumption in the question at the beginning:
you forced others to select a number to represent True / False
which you assumed that a number can represent True / False in the underlying assumption! (and to the other side, True/False can represent a number?)
but the fact is, True / False are not numbers!
I understand you do it so as to teach Excel's logic, but please don't take it simply due to your pre-set assumption!
(as topics of interest, if you are designing a questionnaire, you have to avoid this prescribed situation!)

But this IS Excel so lets puts our Excel hats back on. We know that any arithmetic expression must yield a numeric result. We know therefore Excel must attempt to interpret each operand as a numeric result. We know that if it cannot resolve the operand it will yield a #VALUE! error, a very specific error that is aim-matching! We know that double negation is quicker. We know that it is indiscriminate; and that as VALUE() is only partially discriminate we must resort to a more explicit type test. We know that everyone around the world, using EXCEL is using this method.

Yes, this is Excel, but no matter what Excel allows you to do, think if it's reasonable.
I don't mention again that you emphasize the output of double negation
here at least you mentioned about the input of double negation
but please don't talk about aim-matching regarding to error only! (you are mixing indiscriminate with aim-matching here! error = due to indiscriminate, but then you talk about aim-matching)
my aim-matching does not mean this,
please revisit my statement about the main aim of negative & the side effects
and don't skip away from it if you want to beat me in this point

Alvin I'm going to put it very bluntly! You do not possess the requisite experience nor knowledge of this topic to even begin suggesting changes to conventions. These suggestions could adversely affect people, after all we many many millions of Excel users around the world with access to the internet. On this matter you are wrong wrong wrong! Our conventions have long been around. They have been challenged, debated and ratified! Yes there are some that could do with improvement; but unless you open yourself up to the notion of LEARNING and spend some quality time on the forums (months if not years) then you will NEVER possess the requisite skills and knowledge to accurately challenge these. LEARN the stuff buddy and then come back with a proper notion that might actually improve the way we use Excel.

see what my statements above mean
As to say, I am waiting for you to beat my points above if you could.
I think it's you to learn at this stage.

From the beginning I have been emphasizing on these two points!


BTW, I don't think answering questions here would give much use to Excel design knowledge, mostly the topics here are requiring one-cell solution which wouldn't help in designing. and topics here only require the result.
or what else I can see is the complicated long formulas and array formulas.
the main aim here is to help people achieve their end result. So in contrast, it's of little benefit if one hopes to learn from here about best practice and design

i.e. technologically it is ok here (the use of functions and array formulas, the complexity is wonderful), but not on the design side.