Paul recently asked at j-walkblog.com
…In Excel, if I put “Paul” in cell A1, and 100 in cell A2, and in cell A3, I put in the formula “=A1+A2? I get #VALUE, however if I put in “=SUM(A1:A2)” I get 100. Isn’t summing adding? Why does one return a number and the other gimme #VALUE?
Here’s Dick’s theory: When you use an operator (e.g. +), you supply the numbers (operands?) on which to operate explicitly. “=A1+A2? – A1 and A2 are explicitly entered by the user, so there’s no need to “handle” non-numeric entries. If you don’t want an error, don’t include that cell reference in your formula. With SUM, however, you supply a range, not individual cells. In order to make SUM easier to use, it handles non-numeric entries in a specific and consistent way. If you had to split up all the ranges just to avoid some text, you SUM formulas would be cumberSUM formulas.
This is how it probably happened:
The developer started by programming the plus sign. He programmed it to return an error when one of the operands was non-numeric. Later, he invented the SUM function. He programmed that to return an error when a value in the array (read: the value of a cell in the range) was non-numeric. Then it went to alpha testing and a bunch of people said, “Every time I use SUM I get a VALUE error. Can’t you make it easier to use SUM?” To which he said, “Get out of my office, you whiners!” Later, after he had a sufficient amount of caffeine, he reconsidered and added special handling to the SUM function.
Wow, I’m practically famous now. First I get top billing on J-Walk’s Ask J-Walk, now a Daily Dose of Excel post with my name on it. I don’t know if you noticed the comment I left at J-Walk, but I was messing with VisiCalc circa 1979 and it lets you use the plus operand or the sum function with text. Why do I care about this? When auto-filling formulas it’s a pain to get #VALUE on some of the rows. I could always write a better formula and check for #VALUE (which I normally do) but I was just curious about it.
Actually, Excel will add text and numbers. Set A1 to 100 then set A2 to ‘100 (note the leading quote which makes this entry “text”). You can set A3 to A1 + A2, it will convert the text to a number, and then place the number 200 in A3. The issue is that Excel will indeed try to cast text to a numerical value in this context and will fail if unable to make the conversion. If you set A4 to =SUM(A1:A2), the answer will be 100. It won’t even try to convert any text entries.
Here is a variation on this issue in Excel. I like to distinguish between
student grades of zero on a quiz and missing the quiz so that I can provide
more accurate student advising:
A B C Formulas in C
1 StudentQuiz 1 RawQuiz 1 Percent
2 Alice 9 75.00% =(B2/12)
3 Bob Absent #VALUE! =(B3/12)
4 Fred 8 66.67% =(B4/12)
Not only is Bob’s mark not calculated by Excel, but because the formula in C3
returns an error, no summary arithmetic can be performed on Column C.
Solution 1: Replace formulas in C with =(SUM(B2:B2)/12), etc.
Solution 2: Select Tools | Options | Transition | Sheet options and check the
“Transition formula evaluation” box. Lotus 1-2-3 evaluates
strings as zero and will thus return 0.00% for Bob’s mark while
allowing the raw “score” of “Absent.”
Solution 1: I will often have a check for a numeric value or an error in this formula.
Solution 2: I’d be reluctant to make this change, not knowing what else would be calculated differently.