if input a number at cell A1,such as 2345,i want to calculate the sum of every bit(=2+3+4+5),firstly,read bits by MID(A1,n,1),but the result should by string,right?
how to convert to int and add up?
i need a macro
No, you don’t need a macro. If your 2345 is in A2 then write =SUMPRODUCT(VALUE(MID(A2;ROW(INDIRECT("A1:A"&LEN(A2)));1))) and get your 14
2 Likes
it’s powerful.thank you so much.
Assuming you need a digit sum of some kind, you do not need any “macro”, just a formula.
If you want the simple sum (unreduced), the solution is to build an array on the fly, using INDIRECT() or OFFSET(), and sum that. Already answered here.
That approach can also be used iteratively to create a reduced (single digit) sum, but in the decimal number system this sum will correspond (roughly) to the remainder of division by 9. See this answer.
- What you are talking of is the sum of digits (SOD now; not sum of bits)
- Why should you need a “macro”? Do you talk of a UserDefinedFunction (UDF)?
- Often the “iterated sum of digits” is wanted. The formula then is
=MOD(A1; 9) +IF(CURRENT()=0;9;0). From a mathematical point of view the second addend is superfluous / should be omitted. The result then is the reminder you get when dividing by 9 (the MOD function is made for). - The non-iterated SOD you can get by
=SUMPRODUCT(VALUE(MID(A1;COLUMN(OFFSET(INDIRECT("A1");0;0;1;LEN(A1)));1))).
1 Like
thanks.sumproduct is enough for my purpose.learned much from your reply.you are so kind and spare nothing.
Or (non volatile):
=SUMPRODUCT(--MID(A1; ROW(A1:INDEX(A:A;LEN(A1)));1))
=SUMPRODUCT(--(0&MID(A1;ROW(A1:A99);1)))
1 Like
Of course, what if you want the sum of the digits for a representation other than base 10? Then I think a UDF (macro) is in order. Here’s one I tried to make a little elegant.
Function SOD(DecimalValue As Variant, Optional BaseForSum As Variant) As Variant
Rem Returns the decimal sum of the digits in the BaseForSum representation of DecimalValue
Dim Value As Variant
Dim Digit As Variant
Dim Place As Variant
Rem Assume base 10
If IsMissing(BaseForSum) Then
BaseForSum = CDec(10)
Else
BaseForSum = CDec(BaseForSum)
Endif
Rem Find the first power of BaseForSum greater than DecimalValue
Value = CDec(DecimalValue)
Place = CDec(1)
While Place < Value
Place = Place * BaseForSum
Wend
Rem Divide out the digits and accumulate as a sum
SOD = 0
While Value >= 1
Place = Int(Place / BaseForSum)
Digit = Int(Value / Place)
Value = Value - (Digit * Place)
If Digit = BaseForSum Then Digit = 1
SOD = SOD + Digit
Wend
End Function
Taking full advantage of the ‘Decimal’ representation (CDec’s) is a little tricky, up to passing in the DecimalValue as a String.
Sub SODTest()
MsgBox SOD("123456789123456789" ,10)
End Sub
correctly returns 90. But without the quotes it incorrectly returns 67 due to loss of precision. You get used to CDec-style algorithms, but there are plenty of pitfalls. Still, things should work pretty normally as number or string for the range of values you might encounter daily without any special care. But passing as a String would let you do some modifications to use other bases for input, especially bases greater than 10, i.e. hypothetical SOD(“AA4”,16,14).
P.S. This does sum up the number of bits, just use 2 as the base. 
spectacular.originally,i think i need such codes.