Sum up a number by bits

English
#1

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?

#2

i need a macro

#3

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
#6

it’s powerful.thank you so much.

#4

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.

#5
  1. What you are talking of is the sum of digits (SOD now; not sum of bits)
  2. Why should you need a “macro”? Do you talk of a UserDefinedFunction (UDF)?
  3. 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).
  4. The non-iterated SOD you can get by =SUMPRODUCT(VALUE(MID(A1;COLUMN(OFFSET(INDIRECT("A1");0;0;1;LEN(A1)));1))).
1 Like
#7

thanks.sumproduct is enough for my purpose.learned much from your reply.you are so kind and spare nothing.

#9

Or (non volatile):

=SUMPRODUCT(--MID(A1; ROW(A1:INDEX(A:A;LEN(A1)));1))

=SUMPRODUCT(--(0&MID(A1;ROW(A1:A99);1)))
1 Like
#8

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. :upside_down_face:

#10

:saluting_face:spectacular.originally,i think i need such codes.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.