Why do people use SUM() unnecessarily?

J

joeu2004

Someone said:
I need a formula that will show percent change from the
pervious month, the one I came up with is =SUM((D19-B19)/B19)
Click to expand...

This is just one example of many that use =SUM() unnecessarily.
Presumably =(D19-B19)/B19 is just as good, if not better
performance-wise.

Why is this "screwy" idea so pervasive, namely using SUM() to
bracket any arithmetic expression? Is there a lousy text on the
market that is giving misleading instruction? Is this perhaps a
carryover from requirements of ancient spreadsheet software
(e.g. Visicalc)? (Not as I recall.)

I know: I shouldn't care. But I hate to see people learn poor
programming technique.
 
C

CLR

All you say is true, "but", the important thing IMHO is that the person be
able to come up with a solution/formula that THEY can understand. So, if at
their particular level of expertize if they feel comfortable with the SUM(),
then so be it. Each of us needs a solution that solves our problem. It may
not always be the most efficient, or "best" solution, but is in such a form
that we can come back to it next month or next year and still understand it
and be able to edit it for our current needs. As we each use Excel more and
more, we eventually learn to do things "better".

Vaya con Dios,
Chuck, CABGx3
 
J

joeu2004

CLR said:
All you say is true, "but", the important thing IMHO is that
the person be able to come up with a solution/formula that
THEY can understand.
Click to expand...

I disagree. Anyone who uses a function called "SUM" to do an
operation other than addition does not understand squat! At
least some people do something like SUM(A1+B1), which is not
quite so nonsensical.

Actually, in most cases, the (mis)user of SUM() did not sound
like an native English speaker. So the use of "SUM" might be
irrelevant to the (mis)user. They might think of "SUM" as
"compute ...".

But that really wasn't the crux of my question. I want to know
why so many people misuse SUM() in this way. I suspect that
someone (or some text) is misleading these people. If it is a
text, wouldn't it be nice to track down the publisher and let them
know that the author is .... (Expletives deleted.)
 
J

JLatham

I agree with CLR - the really important thing is that they understand what
they've done and be able to make alterations to it that work when maintenance
or extension of features is required.

What you are discussing is a matter of both knowledge of the language and
style.

As the people who "misuse" the language get more familiar with it and see
other functions or code implemented they will learn 'cleaner' ways of doing
things.

But for someone trying to be self-sufficient, having something that works
and that they can maintain is much more important than whether they did it in
great style or not. But that's just my opinion, and like coding styles, we
each have our own.
 
J

joeu2004

JLatham said:
I agree with CLR - the really important thing is that they understand
what they've done
Click to expand...

You and CLR both misunderstand my original question. I am not
criticizing people for using SUM() in this "unorthodox" manner.
I am asking: whatever possessed anyone, much less __a_lot__
of people, to misuse SUM() in this manner? Why SUM()? Why
not MAX() or MIN(), for example?

They "must" be getting this idea from somewhere. Where!?

This is a "Genesis" question. Not "what is the meaning of life?",
but "where did life come from, in the first place?".

So far as I can tell, there is no way that anyone learning Excel
for the first time would, on their own, stumble upon SUM() as
the "universal" function for all arithmetic. On the contrary, I can
imagine people writing expressions like "A1+A2+A3+...+A26"
long before some kind soul tells them they can do SUM(A1:A26).

And if you suggest that once they learn of SUM() for that purpose,
it is "logical" that they would apply it to all expression, I would
have to disagree strongly. If we are assuming extremely little
(apparently almost no) understanding of formula design in Excel,
it is a huge leap from SUM(A1:A26) to SUM(A1-B1) (an oxymoron),
much less SUM((A1-B1)/B1), especially for anyone with so little
understanding of the language that they cannot imagine that
=(A1-B1)/B1 would do the same job.

So on the contrary, I suspect that someone has taught people
that SUM() is the "best" way to write an expression for __some__
reason.

I wish someone would tell me that reason. Honestly, I cannot
imagine one. And I am usually very good at ferreting out the
origin of misunderstandings of all kinds, especially linguistic and
cultural. This one has me stumped.
 
B

Bob Phillips

I think the problem is that many people approach their problems thinking
that if they want to get a solution, they need a function. If you are going
to do an arithmetic operation, then you need an arithmetic function, and SUM
is the only obvious example. IMO, it is because of this pervasive attitude
that anyone can pick up a spreadsheet and start using it without any
training. They may be able to do, but for most, it takes training to use it
properly, to understand properly its capabilities, and to avoid the
'obvious' pitfalls.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
D

David Biddulph

[email protected] said:
You and CLR both misunderstand my original question. I am not
criticizing people for using SUM() in this "unorthodox" manner.
I am asking: whatever possessed anyone, much less __a_lot__
of people, to misuse SUM() in this manner? Why SUM()? Why
not MAX() or MIN(), for example?

They "must" be getting this idea from somewhere. Where!?

This is a "Genesis" question. Not "what is the meaning of life?",
but "where did life come from, in the first place?".

So far as I can tell, there is no way that anyone learning Excel
for the first time would, on their own, stumble upon SUM() as
the "universal" function for all arithmetic. On the contrary, I can
imagine people writing expressions like "A1+A2+A3+...+A26"
long before some kind soul tells them they can do SUM(A1:A26).

And if you suggest that once they learn of SUM() for that purpose,
it is "logical" that they would apply it to all expression, I would
have to disagree strongly. If we are assuming extremely little
(apparently almost no) understanding of formula design in Excel,
it is a huge leap from SUM(A1:A26) to SUM(A1-B1) (an oxymoron),
much less SUM((A1-B1)/B1), especially for anyone with so little
understanding of the language that they cannot imagine that
=(A1-B1)/B1 would do the same job.

So on the contrary, I suspect that someone has taught people
that SUM() is the "best" way to write an expression for __some__
reason.

I wish someone would tell me that reason. Honestly, I cannot
imagine one. And I am usually very good at ferreting out the
origin of misunderstandings of all kinds, especially linguistic and
cultural. This one has me stumped.
Click to expand...

I agree entirely with your question. The unnecessary use of SUM() annoys me
too, and the more often it is left unquestioned, the more we will see naive
users assuming that this is correct usage.

I will wait eagerly to see whether you get an answer as to where this
incorrect usage originated.
 
D

Dave Peterson

I think that one of the first things people learn is how to add two cells:

=sum(a1+a2)

And if
 
D

Dave Peterson

oops.

I think that one of the first things people learn is how to add two cells:

=sum(a1+a2)

And if =sum() works with addition, it's got to work with other operators. It's
the universal function for math.
 
J

joeu2004

Dave Peterson said:
I think that one of the first things people learn is how to add
two cells: =sum(a1+a2)
Click to expand...

Okay. Dovetailing Dave's idea with Bob's, I would speculate
that one of the first uses of formulas that people encounter
is summing a column of numbers, such as a balance sheet.
The form of such a spreadsheet is a column of cells with simple
numbers followed by a cell of the form =SUM(A2:...) or
=SUM(A2+A3+...). People might conclude that arithmetic
expressions must always be enclosed in a function, and SUM()
might be the first or only function they are exposed to. This
conclusion might be reinforced when they see responses in
these forums that misuse SUM() as well.

So if we accept Dave and Bob's theory, it becomes all the
more important that when we see this misuse of SUM() in
postings, we explain that SUM() is unnecessary, rather than
simpy accept it tacitly, much less defend it, as just another
"coding style". It is not "just a matter of style" when someone
names a constant variable TWO and assigns it the value of 3,
even though that might work just fine in his application.
 
B

Bob Phillips

I think that advice is offered quite frequently Joe, and whilst I cannot
speak for Dave, I cannot see it as big an issue as you seem to. I don't like
it personally, but that is almost irrelevant IMO.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
C

CLR

Right on Bob!......and as I see where an OP has made a concerted effort to
write their formula and would work except for some small difference, I
personally will continue to enlighten them of only the problem and not pop
their bubble for coming so close...... If they are going to continue much
with Excel, they will learn, with time.........if they are not, it makes no
matter.

Vaya con Dios,
Chuck, CABGx3
 
J

JLatham

To me, it's not so much that I "don't like it" - it works, and I'm ok with
that. But when I encounter such a thing, I try to take the opportunity to
tactfully show them an "easier" way to do it. I know some people that don't
indent their code, as in inside of loops, IF THEN statements, etc, and that
just plain makes me crazy with I try to read the code. But I'm not going to
excommunicate the person for not following my standards - as long as they
aren't working on my programming team at least. These kinds of things are
why organizations set up things like programming standards and language use
guidelines and naming conventions.
 
J

JLatham

In a way it very well may be Microsoft via Excel Help teaching people to do
it that way.

If you start off searching Excel Help for "add two numbers" you eventually
end up here:
http://office.microsoft.com/en-us/assistance/HP030561151033.aspx
and if you look there, the first example simply adds 2 numbers, as =2 + 5,
but the very next example uses the autosum feature and that's where they
start referencing cells, so perhaps people are getting the idea that to work
with cell values they must encapsulate them within the SUM() function?
That's my best guess. To get an absolute answer, you'd have to go ask people
who do it that way WHY they do it that way.
 
J

Jay

Just to let you know, if someone is relatively new to excel, and even
newer to using formulas, they could potentially be mislead by the help
guide in excel. When you go into the help file to learn formalas, it
does not bread down every math equation effectively. So the real issue
you seem to have seems to be with microsoft, and the way they develop
their help files. Important issue to bring up, considering they make
the software, but then again, if they answered every question in the
help file, who would need people like us.
Cheers,
Jason
 
B

Bob Phillips

If they could even every possible question, they would be bigger, better and
richer than they are. That is an impossible ask IMO <G>

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
J

joeu2004

JLatham said:
In a way it very well may be Microsoft via Excel Help teaching
people to do it that way. If you start off searching Excel Help
for "add two numbers" you eventually end up here:
http://office.microsoft.com/en-us/assistance/HP030561151033.aspx
and if you look there, the first example simply adds 2 numbers,
as =2 + 5, but the very next example uses the autosum feature
and that's where they start referencing cells, so perhaps people
are getting the idea that to work with cell values they must
encapsulate them within the SUM() function?
Click to expand...

At this point, we are beating a dead horse. But just some clarifications.

First, that web page is all about "adding", which of course is
consistent with the SUM() functionality. While I retch at things
like SUM(A1+A2), at least that is not incongruous. But I hasten
to point out that the first example of cell references on that web
page shows SUM(B2,B5,B7). So it seems unlike that that web
page leads to the misuses we see.

Second, I was talking about oxymoronic uses of SUM() -- for
example, to encapsulate an expression that computes a ratio or
a difference, things that have nothing to do with SUM. I see
nothing on that web page that would lead someone to those
misuses. Indeed, if they learned Excel by reading the "add
numbers" web page, they probably also looked at "subtract
numbers", "multiply numbers" and "divide numbers", all of which
show examples of formulas of the form =A1-A2, =A1*A2 and
=A1/A2.

(Arguably, the closest thing to misguiding people is the suggestion
to use SUM to "subtract numbers in a range". The text is very
clear that this applies when the range includes negative numbers;
ergo, we are not really subtracting (klunk!). But I can see where
this explanation might lead to some misunderstanding.)

Third, to compare these incongruous misuses of SUM() to style
differences like indentation (as you did in another posting) is like
comparing apples and oranges. Indentation is indeed a very
subjective thing. The lack of or overuse of it does not reflect
any misunderstanding. I think my comparison with TWO having
the value of 3 is more relevant.
That's my best guess. To get an absolute answer, you'd have
to go ask people who do it that way WHY they do it that way.
Click to expand...

Frankly, I was hoping that some of them would respond to this
thread.

I would like to return to my theory that something in nascent
spreadsheet software might mislead people. I am struggling
to remember the original Visicalc. I remember that Multiplan
introduced significant improvements, which I preferred. I do
not remember what they were. But Visicalc was introduced
on computers with very limited memory by today's standards.
It would not surprise me if its parsing algorithms were primitive,
and perhaps (WAG) it required that expressions be a function
parameter in order to aid recognition. Does anyone remember
the original Visicalc syntax for expressions?

Anyway, as I said, we are (I am) beating a dead horse. I was
just curious.
 
T

tanewha69

When I started using excel and started to learn how to do formulas etc
the first thing I learned was how to 'quickly' sum a set of cells wit
the Autosum feature rather than actually writing the formula. Whe
using Autosum which I used most frequently in the beginning i
automatically starts with SUM at the beginning of the formula. Now I
not knowing any better, naturally assumed that that was how a simpl
formula was started. I assumed that the formula had to have some kin
of command at the start to tell it what to do, so I thought SUM wa
needed when calculating something. So I think, and I may be wrong as
am still only learning, that excel itself is the genesis of this wit
the autosum feature... how many people that start using excel wil
learn to actually write formulas BEFORE using autosum? And if someon
is not taught to do it differently then they will continue to do it th
way they have always done it..
 
J

jermsalerms

the reason is simple.

Most excel users learn from trial and error....not reading a book.

When a beginner or intermediate excel user wants to write a formula
they will usually start with the "Autosum" button in the toolbar.
Immediately the formula starts with SUM(). As a beginner many people
will assume that this SUM() is necessary in order to calculate the
equation.

As time goes on and the user become more advanced this idea sticks with
them.

So it's Microsoft that is in a way misleading users.
 
Top