Microsoft KB Archive/108107: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
| Line 44: | Line 44: | ||
== SUMMARY == | == SUMMARY == | ||
Although there is currently no built-in formula that finds the sum of the digits of a number in Microsoft Excel, you can use the formula in the | Although there is currently no built-in formula that finds the sum of the digits of a number in Microsoft Excel, you can use the formula in the "More Information" section in this article to do this. | ||
</div> | </div> | ||
| Line 84: | Line 84: | ||
example, consider MID(849,{1;2;3},1) from the | example, consider MID(849,{1;2;3},1) from the | ||
above paragraph. This will return | above paragraph. This will return | ||
{ | {"8";"4":"9"}. Notice that all the numbers are | ||
text inside the array. | text inside the array. | ||
VALUE() Will change text to numbers. In this example, | VALUE() Will change text to numbers. In this example, | ||
VALUE({ | VALUE({"8";"4":"9"}) returns {8;4;9}. This | ||
allows the numbers to be summed. | allows the numbers to be summed. | ||
| Line 95: | Line 95: | ||
The second formula, which sums the digits of negative numbers, works in the same manner except that it compensates for the initial minus sign (-) in the number.<br /> | The second formula, which sums the digits of negative numbers, works in the same manner except that it compensates for the initial minus sign (-) in the number.<br /> | ||
<br /> | <br /> | ||
'''NOTE''': In each formula, the | '''NOTE''': In each formula, the "$A$1" and "$A$2" are always used, regardless of which cell's digits are being summed. | ||
</div> | </div> | ||
| Line 102: | Line 102: | ||
== REFERENCES == | == REFERENCES == | ||
"Function Reference," version 4.0, pages 274, 299-300, 365-366, 423, 444. | |||
</div> | </div> | ||
Latest revision as of 10:20, 20 July 2020
Article ID: 108107
Article Last Modified on 8/15/2003
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q108107
SUMMARY
Although there is currently no built-in formula that finds the sum of the digits of a number in Microsoft Excel, you can use the formula in the "More Information" section in this article to do this.
MORE INFORMATION
The following formula returns the sum of the digits of a positive number contained in cell A10:
=SUM(VALUE(MID(A10,ROW($A$1:OFFSET($A$1,LEN(A10)-1,0)),1)))
The following formula returns the sum of the digits of a negative number contained in cell A11:
=SUM(VALUE(MID(A11,ROW($A$2:OFFSET($A$2,LEN(A11)-2,0)),1)))
NOTE: The above formulas must be entered as array formulas. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+RETURN.
Explanation of the Formula
The following information assumes that cell A1 contains the number 849.
This part of the formula Does this
------------------------------------------------------------------------
A$1:OFFSET($A$1,LEN(A1)- Creates a reference of cells going down a
1,0) column that has the same number of cells as
the number in A1 has digits. For example, the
formula $A$1:OFFSET($A$1,LEN(A1)-1,0) would
return $A$1:$A$3 since LEN(A1)-1 equals 2 and
OFFSET($A$1,2,0) returns $A$3.
ROW() Returns the row number of the cell reference.
If there is more than one cell in the
reference, it will return an array. In this
case, ROW($A$1:$A$3) returns {1;2;3}.
MID() Returns a portion of a text string. By using
an array for one of the arguments, we can
return multiple text strings in an array. For
example, consider MID(849,{1;2;3},1) from the
above paragraph. This will return
{"8";"4":"9"}. Notice that all the numbers are
text inside the array.
VALUE() Will change text to numbers. In this example,
VALUE({"8";"4":"9"}) returns {8;4;9}. This
allows the numbers to be summed.
SUM({8;4;9}) Returns the final result, 21.
The second formula, which sums the digits of negative numbers, works in the same manner except that it compensates for the initial minus sign (-) in the number.
NOTE: In each formula, the "$A$1" and "$A$2" are always used, regardless of which cell's digits are being summed.
REFERENCES
"Function Reference," version 4.0, pages 274, 299-300, 365-366, 423, 444.
Additional query words: 2.0 2.10 4.00a 5.00a 5.00c 7.00a 97 XL97 XL7 XL5 XL4 XL3 XL
Keywords: kbhowto KB108107
