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

 

[FormR]

I would like to have a fruitful discussion

I wish I didn't get involved - but either way; all points have been addressed and there is nothing left here that is fruitful.

 

[MARK858]

As similar to situation of Jon von der Heyden
He also said he would not participate, but at the end he does!

But then I'm not Jon.

 

[Peter_SSs]

this is another issue
that worksheet is "PROCESS" in the cycle of "INPUT" -> "PROCESS" -> "OUTPUT"
if one is going to print it out, that should be an "OUTPUT" which contains no calculation logic in the process

Well, that’s your view, but this forum is made up of about 226,500 members who have posted at least once. Of those, about 224,700 have less than 200 posts. I don’t think it unreasonable to assume the vast majority of those (who are the members who ask most of the questions) are not highly skilled in Excel (your good self excepted of course) and couldn’t care less about that and, if printing, are likely to print the sheet as is. They just want a reasonable answer to their question.

Talking about the ease of maintenance, think when you are working on it.
If you are adding records there, you clearly know you have to copy down those helper columns, right?

Right. But by your own question ..

.. how can you sure the file won't be exposed to others?

.. and where I work, many of my (Excel novice) colleagues who would likely be adding the data would not find that obvious or trivial - they just expect to add their data and have Excel do the rest - and it does.

e.g. there are two values of interest, as shown below (note: there can be more)
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000=H1), J6:J2000)
and
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000=H1), --(F6:F2000=F1), J6:J2000)

A. First, to get the second result as well as the first ..

SUMPRODUCT: Decide which cell to put the new formula in, Copy/Paste the text of the original formula & add the red bit. Took me about 15 seconds (I’m not fast on the keyboard)
Your layout: Decide on a new helper column to use (I used column A with your original layout simply because that was quickest (I know it makes a poorer layout now), put the new formula =IF(F6=F$1,1,0) in A6, amend the formula in B6 to =A6*C6*D6 by adding the red bit, copy both formula down to the bottom of my data (row 1097). I did that by double clicking the Fill Handle but still had to check that the new formulas populated to the end of the data in case another user had inserted blank rows in my data. Time > 15 seconds.

B.(i) Now change the condition in column H to “<>H1”. If more than 2 or 3 results required this change, then yes, your layout would be a little faster to modify, but not a lot.

B.(ii) Let’s consider a different change though. Suppose we have this result
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000=H1), --(F6:F2000=F1), J6:J2000)
And I want to keep that result but also have this one
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000<>H1), --(F6:F2000<F1), J6:J2000)

SUMPRODUCT: Decide on formula cell, Copy/Paste original formula text, modify the red.

Your layout: Find 3 new columns to use (say L,M,N) as you still need the original ones for the first result. 3 new formulas:
L6: =IF(H6<>H$1,1,0)
M6: =IF(F6<F$1,1,0)
N6: =C6*L6*M6
All copied down 1,000+ rows
Decide on new result cell, new formula: =SUMIF(N$6:N$1097,1,J$6:J$1097)

No prizes for guessing which is fastest.


You mentioned the possibility of having up to 10 SUMPRODUCT formulas to maintain. If that were these case, and the conditions varied for each, I’d have to say I would much rather maintain those 10 formulas than maintain, who knows .. 20, 30, 40, … helper columns as they need to be added to accommodate the various conditions & combination of conditions as I've just demonstrated.



Now let’s return to some points you tried to make in your first post

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

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 --

by immediatelly looking the formula, do you find it trival to understand the formula?

Quite a few mentions of aim, meaning, ease of understanding. So let's have a look at those things with this exercise.

SUMPRODUCT
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000<>H1), --(F6:F2000<F1), J6:J2000)

Looking at this I (& I contend anybody even reasonably familiar with SUMPRODUCT) immediately can see that this is summing column J where col E matches E1, col H does not match H1 and col F is less than F1.
It matches the aim of the required result, the meaning is clear, it is easy to understand.

Your Layout
=SUMIF(N$6:N$1097,1,J$6:J$1097)

Looking at this I can see immediately that this is summing column J where column N contains a 1.
The formula itself is clear and easy to understand, it just tells me nothing about the aim.
I have to backtrack through the formulas in column N then C, L & M before I can eventually deduce the aim.



Just two further comments:
We are only talking about SUMPRODUCT for this example question, because you are using Excel 2003. We could/should be using more efficient functions than SUMPRODUCT if using later versions. I find it interesting that you say you make your living from Excel but you are using a 10 year old version of the product when 3 newer versions exist.

Now I’m finished here too and am going back to answering questions.

 

[aaalviny]

Well, that’s your view, but this forum is made up of about 226,500 members who have posted at least once. Of those, about 224,700 have less than 200 posts. I don’t think it unreasonable to assume the vast majority of those (who are the members who ask most of the questions) are not highly skilled in Excel (your good self excepted of course) and couldn’t care less about that and, if printing, are likely to print the sheet as is. They just want a reasonable answer to their question.

About "Input" -> "Process" -> "Output", Jon and I were not talking about how we should give our answers to those asking questions. but I believe it's just our own discussion about the layout design freedom offered by Excel and how we are using it.

Furthermore, "Input" -> "Process" -> "Output" is a basic knowledge to every system (Excel is also one of it)
Google for it and I think you will see many results.
e.g. Input Process Output
And for every workbook, "Input" -> "Process" -> "Output" is just an integral part.
Of course, whether one mixes any of above in one place is another issue.

Finally, I think we are here not only to answer others' questions, but to share our thoughts on problem solving to further equip ourselves. and to create best practices (otherwise how best practices are created?)
and people here are always embedding their best practice in their solutions provided!

Right. But by your own question .. .. and where I work, many of my (Excel novice) colleagues who would likely be adding the data would not find that obvious or trivial - they just expect to add their data and have Excel do the rest - and it does.

here I would like to clarify. There are two ways we make our excels for
1. work for a task, it may be reviewed by peers / higher-level managers. The peers may possess similar excel skills to conduct a check on the working and results, while the higher-level managers may emphasize more on the reasonableness, at the same time he/she may also possess certain excel skills
2. send out for other parties to use

for what you are mentioning about letting other novice users to simply input the data and get the result, it should be point 2 above.
usually in that case, of course the maintenance is more complicated, and due to the fact that they are novice users,
in my workplace, we will simply protect the workbook, and state some restrictions (all in all you won't consider row 65536 for all the formulas for 2003, e.g. may limit the number of data to 1000.)
and may include something like =IF($A2="", "", ABC) for the data validation process.
and I would like to add that, even SUMPRODUCT is used, does the user know he/she has to edit the formulas there?
I think we are not talking about how we release our workbooks to other novice users which simply only know to input data but with nearly no Excel knowledge.

And clearly, you may guess, I am talking about point 1, which is more important I believe.
my peers and managers are just possessing a good knowledge to the formulas (of course not about those complicated array formulas, we tend to avoid it actually)

From your reply, I wonder what your role is? design a workbook for other users to input data, but the "other users" are the same team as yours? Then can I ask why your colleagues don't go to learn yet if they have to do that job, given you are in the same team? (I know it's a bit personal, you may choose not to answer if you don't want to)

A. First, to get the second result as well as the first ..

SUMPRODUCT: Decide which cell to put the new formula in, Copy/Paste the text of the original formula & add the red bit. Took me about 15 seconds (I’m not fast on the keyboard)
Your layout: Decide on a new helper column to use (I used column A with your original layout simply because that was quickest (I know it makes a poorer layout now), put the new formula =IF(F6=F$1,1,0) in A6, amend the formula in B6 to =A6*C6*D6 by adding the red bit, copy both formula down to the bottom of my data (row 1097). I did that by double clicking the Fill Handle but still had to check that the new formulas populated to the end of the data in case another user had inserted blank rows in my data. Time > 15 seconds.

Here, let me clarify what I emphasize on.
Everybody has different working speeds in excel. To me these should take less than 10 seconds as I am using only keyboard shortcuts to navigate around the cells and to fill the formulas. and I don't have to check whether the end row has been filled with the required formula. (arrows with Ctrl and Shift to navigate, together with Ctrl + D or Ctrl + Enter to fill the formulas)
The point is not the working speed I suppose, but whether one knows what to do and on the speed of the calculation.
and more importantly, the clear flow of calculation I would say, and this is to be supplemented below.

B.(i) Now change the condition in column H to “<>H1”. If more than 2 or 3 results required this change, then yes, your layout would be a little faster to modify, but not a lot.

B.(ii) Let’s consider a different change though. Suppose we have this result
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000=H1), --(F6:F2000=F1), J6:J2000)
And I want to keep that result but also have this one
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000<>H1), --(F6:F2000<F1), J6:J2000)

SUMPRODUCT: Decide on formula cell, Copy/Paste original formula text, modify the red.

Your layout: Find 3 new columns to use (say L,M,N) as you still need the original ones for the first result. 3 new formulas:
L6: =IF(H6<>H$1,1,0)
M6: =IF(F6<f$1,1,0)< strong=""></f$1,1,0)<>
N6: =C6*L6*M6
All copied down 1,000+ rows
Decide on new result cell, new formula: =SUMIF(N$6:N$1097,1,J$6:J$1097)

No prizes for guessing which is fastest.

In summary, there are just two sides of whether which design is faster to modify. But again I have to supplement below about this point.

You mentioned the possibility of having up to 10 SUMPRODUCT formulas to maintain. If that were these case, and the conditions varied for each, I’d have to say I would much rather maintain those 10 formulas than maintain, who knows .. 20, 30, 40, … helper columns as they need to be added to accommodate the various conditions & combination of conditions as I've just demonstrated.

As I mentioned long time ago, some criteria can be combined to become 1 criteria to use only 1 columns, if it's appropriate. E.g. to check whether a date is within this year and before today, this can be combined to 1 cell. Or if there are any others situations that the two criteria won't be separating from each other. All in all it's very rare to have over 5 to 6 of different conditions which are not complementary event to other conditions. As from my working environment as well as the questions asked here (but these 5 to 6 different conditions can give >10 SUMPRODUCT formulas, as a combination of Event A true, Event A false, Event B true, Event B false, etc.)
and if it really requires 20 or 30 of helper columns, I think the designer has to think about why those are needed. and what are the problems underlying those helper columns, if those helper columns are required, I don't expect the SUMPRODUCT would be simple as well.

Let me demonstrate another example, about the ease of maintenance and why "Don't Repeat Yourself" matters here.
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000<>H1), --(F6:F2000<f1), j6:j2000)
>=F1), J6:J2000)
=SUMPRODUCT(--(E6:E2000=E1),--(E6:E2000 > $E$2), --(F6:F2000>=F1), J6:J2000)
<f1), j6:j2000)
=SUMPRODUCT(--(E6:E2000=E1),--(E$6:E$2000 > E$2), --(F6:F2000>=F1), J6:J2000)
=SUMPRODUCT(--(E6:E2000><e1),--(e6:e2000>E$2), --(F6:F2000>=F1), J6:J2000)

Now in case for the three formulas containing the logic E6:E2000>E2
now it should be replaced with E6:E2000=E2
and yes, those $ exists. all in all you won't know any people accidentally adds $ there, not only you are developing the workbook (I use "develop" here, to distinguish from "user")
and you may have peers like me that who like to add $ in front of the row reference to fix the data
How will you deal with the replacement and there may be some extra spaces there to avoid direct replacement?
it has to be checked one by one for the sake of $ and empty spaces?
however those segments are clearly of the same logic, but multiple replacements / checks are required because of the violation to check for E6:E2000 = E2 once for each SUMPRODUCT formulas.
this logic is duplicated, and so it creates troubles when it requires update.
At least you will feel the replacement can used better if you can ensure there's no $ and spaces, and then you can use replace all, but who knows? given there are multiple formulas containing that logic segment and others may be updating it as well.
</e1),--(e6:e2000></f1),></f1),><f1), j6:j2000)
<f1), j6:j2000)
<e1),--(e6:e2000>

Now let’s return to some points you tried to make in your first post
Quite a few mentions of aim, meaning, ease of understanding. So let's have a look at those things with this exercise.

SUMPRODUCT
=SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000<>H1), --(F6:F2000<f1), j6:j2000)

Looking at this I (& I contend anybody even reasonably familiar with SUMPRODUCT) immediately can see that this is summing column J where col E matches E1, col H does not match H1 and col F is less than F1.
It matches the aim of the required result, the meaning is clear, it is easy to understand.

Your Layout
=SUMIF(N$6:N$1097,1,J$6:J$1097)

Looking at this I can see immediately that this is summing column J where column N contains a 1.
The formula itself is clear and easy to understand, it just tells me nothing about the aim.
I have to backtrack through the formulas in column N then C, L & M before I can eventually deduce the aim.

</f1),></e1),--(e6:e2000></f1),></f1),><f1), j6:j2000)
<f1), j6:j2000)
<e1),--(e6:e2000><f1), j6:j2000)
I would like to clarify here that
I didn't say SUMPRODUCT does not satisfy aim-matching
And I would not say my approach does not satisfy aim-matching as well.

To illustrate
the aim of this job is: (I just optionally type the column chosen, anyway just a variable)
Representation 1: check for each record if col D and col E matches the required criteria, then sum col F
this is how you present with your SUMPRODUCT
Representation 2:
a) check for each record if Col D and col E matches the required criteria
b) if it matches, then sum col F
Representation 3:
a) check for each record if Col D matches the required criteria
b) check for each record if Col E matches the required criteria
c) if it matches both criteria, then sum col F

They all represent the same aim, and my formulas are exactly doing those
but the point is how we represent by how it is broken down to smaller parts
and I have represented the aim by breaking down the parts (e.g. similar to modularization in programming, we can't just say we don't understand the whole thing by merely reading one of the modules!)
and all in all it's not a good practice to put everything in the same Sub (in VBA language)
and that's similar to excel approach I would say
Modularization leads to easier understanding, the same applied to Excel.
In terms of code, we can have shorter lines of code with a clearer identification by the module name
In terms of Excel formulas, we can have simpler formulas with column header to describe the requirement
by pressing F2 in the cell of A2*C2*D2 can already show what are the dependency and what are the criteria
at least I don't think my layout gives a worse readability. (I think mine is better, still, from the reasons above on decomposing formulas)


so due to above, the aim-matching problem is totally different for the case with -- and VALUE()
see discussion in older posts if you wonder how their aim differs
my aim-matching is only dealing with -- and VALUE()

and of course the aim matching exists in
=LOOKUP(10^308,CHOOSE({1;2},0,VLOOKUP(...)))
as well
</f1),></e1),--(e6:e2000></f1),></f1),><f1), j6:j2000)
<f1), j6:j2000)
<e1),--(e6:e2000><f1), j6:j2000)

Just two further comments:
We are only talking about SUMPRODUCT for this example question, because you are using Excel 2003. We could/should be using more efficient functions than SUMPRODUCT if using later versions. I find it interesting that you say you make your living from Excel but you are using a 10 year old version of the product when 3 newer versions exist.

Now I’m finished here too and am going back to answering questions.

</f1),></e1),--(e6:e2000></f1),></f1),><f1), j6:j2000)
<f1), j6:j2000)
<e1),--(e6:e2000><f1), j6:j2000)
I guess you mean SUMIFS()?
use of parenthesis doesn't give a matter given it's clear, e.g.
there are just nearly no difference in understanding SUMIFS and SUMPRODUCT, although their syntax differs
and the parenthesis in SUMPRODUCT is not nested together, that's why the clearness doesn't differ much, comparing:
1. =IFERROR(INDEX($A$2:$A$16,SMALL(IF($I$2:$I$16=1, ROW($A$2:$A$16)-ROW($A$2)+1),ROWS($J$2:J2))),"")
2. =SUMPRODUCT(--(E6:E2000=E1),--(H6:H2000<>H1), --(F6:F2000<f1), j6:j2000)

my aim, probably different from you, I am using excel to create outputs, but not from the prospective as a technical designer/consultant which requires full understanding of different versions of Excel.
But all in all, the design problem should basically be the same across different versions. To supplement, there may be some differences regarding IFERROR which is new in 2007, or maybe SUMIFS, although I find no difference for SUMIFS.
In my working environment, if anyone doesn't possess above-average Excel techniques and doesn't possess fair amount of keyboard shortcuts in Excel (keyboard is used >=90% of the time I would say when working in Excel), I would say he/she can't stay. (of course it can be exempted for people without relevant work experience, but that of course, must only be small potatoes, or extremely high-level managers, and even for small potatoes, they are learning those Excel knowledges and keyboard shortcuts from working as internship or fresh graduates)
All in all, using Excel, no matter in 2003 or later versions, only means to get the job done, isn't it?</f1),></f1),></e1),--(e6:e2000></f1),></f1),><f1), j6:j2000)
<f1), j6:j2000)
<e1),--(e6:e2000><f1), j6:j2000)
</f1),></e1),--(e6:e2000></f1),></f1),>

 

[RoryA]

There's a typo in your conclusion - it should read:

As a conclusion, in my opinion please use VALUE() instead of --, *1, ^1, +0 or INT() for what I and I alone consider Excel best practice

It seems fairly clear that your "best practice" is not a consensus in any way, any more than my suggesting all workbooks should be in 20 point font because it's easier to read would be.

 

[aaalviny]

There's a typo in your conclusion - it should read:

As a conclusion, in my opinion please use VALUE() instead of --, *1, ^1, +0 or INT() for what I and I alone consider Excel best practice

It seems fairly clear that your "best practice" is not a consensus in any way, any more than my suggesting all workbooks should be in 20 point font because it's easier to read would be.

you can say that's my opinion, but please don't extend it to the ground only I alone agree it's the best practice, and I have to say it's clearly not from my environment. (and that actually generally speaking, as an article written by me, it already implies that things are going from my viewpoint, I don't think there's a need to add your wordings)
I admit, it's not a consensus here from all your replies I can see.
However, that's again to mention, the decision is based on how you weigh "aim-matching+easy understanding" and "indiscrimination" versus "efficient" and "the little improvement in speed" (I really have to say little, as this speed is little comparing with the functions that are comparing whether two values are the same e.g. MATCH(,,0) and VLOOKUP(,,,0))

As a final word, 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.
(I had a change in early 2012)

 

[Jon von der Heyden]

Oh my goodness Alvin are you still fighting this corner? You are persistent, I'll give you that!

you can say that's my opinion, but please don't extend it to the ground only I alone agree it's the best practice

Alvin; at the time of writing this there have been 715 views of this thread and 45 replies. That's quite a bit of attention isn't it? And how many people have supported your notion to use VALUE() for coercion? =VALUE(0)!!! =INT(0)!!! =N(0)!!! This really IS your opinion and your opinion alone! And to be frank the notion is at best ridiculous!

 

[RoryA]

you can say that's my opinion

And I did, precisely because that is all it is

as an article written by me, it already implies that things are going from my viewpoint, I don't think there's a need to add your wordings

In my opinion that is a specious argument designed to allow you to state anything you like as though it were fact.

However, that's again to mention, the decision is based on how you weigh "aim-matching+easy understanding" and "indiscrimination" versus "efficient"

Precisely. It's a personal decision and will likely vary from case to case. I personally do not believe there to be one best practice that can be stated regarding legibility versus efficiency. It will always be a trade-off. However, using 41 formulas instead of one just in case there may be changes in future requirements does not appear to be a firm footing for any argument.

Experiencing some changes+shocks and starting to think in a different prospective are a good sign to all, including me.

Now that I actually do agree with wholeheartedly. It is good to question and challenge accepted wisdom as long as both sides are actually listening and not simply conducting two monologues.

 

[aaalviny]

Oh my goodness Alvin are you still fighting this corner? You are persistent, I'll give you that!

Alvin; at the time of writing this there have been 715 views of this thread and 45 replies. That's quite a bit of attention isn't it? And how many people have supported your notion to use VALUE() for coercion? =VALUE(0)!!! =INT(0)!!! =N(0)!!! This really IS your opinion and your opinion alone! And to be frank the notion is at best ridiculous!

Yes Jon, at least, I hope to make a greater influence, at least although I can't beat your efficient and fast issue
and you cannot beat my aim-matching and being indiscriminate on the other hand, right?
I hope to raise a concern about these two advantages, which most people are not aware of
and although most people do not value it, I value it more than the efficient and "fast" issue
(this fast, is opposed to what I think the real issue related to speed is.
Speed, to me, depends on the data structure organization, DRY and correct choice of equality checking (including lookups) mostly)

to quote,

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 a performance 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.

Program optimization - Wikipedia, the free encyclopedia

And I did, precisely because that is all it is

In my opinion that is a specious argument designed to allow you to state anything you like as though it were fact.

Precisely. It's a personal decision and will likely vary from case to case. I personally do not believe there to be one best practice that can be stated regarding legibility versus efficiency. It will always be a trade-off. However, using 41 formulas instead of one just in case there may be changes in future requirements does not appear to be a firm footing for any argument.

Now that I actually do agree with wholeheartedly. It is good to question and challenge accepted wisdom as long as both sides are actually listening and not simply conducting two monologues.

so yes, still the point I am to make
people do not value the future maintenance much. That's the problem I very much would like to emphasize.
Seeing an originally seemingly simple workbook to grow more complex and complex and harder to read, I find this important.
and the "just in case", well, again, no one knows about the future. It's about whether the design can be re-usability and maintained. I must say it's very important, it's lucky that this is well valued in the programming world.

 

[lrobbo314]

Wow.
I hope that today is your day off.