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.