Sum of digits within a cell

Kevin

unread,

Jun 15, 2003, 4:26:58 PM6/15/03

to

For my program, I need to be able to treat the digits of
a number in a single cell as separate numbers themselves,
and be able to sum the digits together to create a new
number. I need to be able to do this recursively, until I
arive at a single digit.

Example:

A cell contains the value 1234
My function would add 1+2+3+4 = 10
then add 1+0 = 1

Does anyone know of a way to do this?

Peo Sjoblom

unread,

Jun 15, 2003, 4:50:29 PM6/15/03

to

One way

=SUMPRODUCT(--(MID(A2,ROW(INDIRECT("1:" & LEN(A2))),1)))

where A2 holds 1234

--

Regards,

Peo Sjoblom

"Kevin" <sonn...@aol.com> wrote in message
news:00eb01c3337c$783498a0$a401...@phx.gbl...

Aladin Akyurek

unread,

Jun 15, 2003, 5:08:08 PM6/15/03

to

Let A1 house the target numeric value...

In B1 enter & copy to D1...

=IF((LEN(A1)>=1)*(LEN(A1)<=15),SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1)))
,1)+0),"")

D1 will house the desired single-digit result.

"Kevin" <sonn...@aol.com> wrote in message
news:00eb01c3337c$783498a0$a401...@phx.gbl...

Lance

unread,

Jun 15, 2003, 5:20:54 PM6/15/03

to

Try a user function

Function addup(ByVal y As Integer)
a = 0
While y > 0
a = a + y Mod 10
y = Int(y / 10)
Wend
If a > 9 Then
a = addup(a)
End If
addup = a
End Function

>.
>

Dana DeLouis

unread,

Jun 15, 2003, 6:41:39 PM6/15/03

to

Here is another idea at a custom function...

Function SumDigits(n) As Double
'// Dana DeLouis
Const dgts As String = "#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#+#"
SumDigits = Round(n, 0)
Do While SumDigits > 9
SumDigits = Evaluate(Format(SumDigits, dgts))
Loop
End Function

HTH.

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"Kevin" <sonn...@aol.com> wrote in message
news:00eb01c3337c$783498a0$a401...@phx.gbl...

Daniel.M

unread,

Jun 15, 2003, 8:57:26 PM6/15/03

to

Hi Kevin,

Assuming integers numbers of 1 to 15 digits:

=SUMPRODUCT(--(MID(SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:" &
LEN(A1))),1)))&"00",ROW(INDIRECT("1:3")),1)))

Regards,

Daniel M.

"Kevin" <sonn...@aol.com> wrote in message
news:00eb01c3337c$783498a0$a401...@phx.gbl...

Myrna Larson

unread,

Jun 15, 2003, 10:36:28 PM6/15/03

to

You can eliminate the recursion by checking for A > 9 inside the loop and subtracting 9 if so. I
also wanted to be able to up to 15 digits, i.e. a double, so Mod -- which is an integer operator
so limited to long integer range -- won't work.

Function AddDigits(ByVal X As Double) As Double
Dim A As Double
Dim Y As Double

A = 0
X = Int(Abs(X))
Do While X > 0
Y = Int(X / 10)
A = A + X - Y * 10
If A > 9 Then A = A - 9
X = Y
Loop
AddDigits = A

End Function

Dana DeLouis

unread,

Jun 16, 2003, 12:22:25 AM6/16/03

to

Thanks Myrna. I didn't catch that pattern. :>)
Here is another attempt without a loop...

Function SumDigits(n) As Double
'// Dana DeLouis

SumDigits = Round(n, 0)
' Mod 9, but for larger numbers
SumDigits = SumDigits - (9 * Int(SumDigits / 9))
SumDigits = SumDigits Mod 9
If SumDigits = 0 Then SumDigits = 9
End Function

--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =

"Myrna Larson" <myrna...@charter.net> wrote in message
news:3q9qevou76mp2jkn2...@4ax.com...

Vasant Nanavati

unread,

Jun 16, 2003, 7:12:36 AM6/16/03

to

Neat, Dana ... I was trying to come up with something like this but gave up
in frustration!

Regards,

Vasant.

"Dana DeLouis" <ng_...@hotmail.com> wrote in message
news:OF3pI87M...@tk2msftngp13.phx.gbl...

Myrna Larson

unread,

Jun 16, 2003, 12:24:14 PM6/16/03

to

The code I showed was modified from what you use to create or check a check-digit for a credit
card number. There you multiply alternate numbers by 2, so there's no way around the loop
approach.

Myrna Larson

unread,

Jun 16, 2003, 12:27:33 PM6/16/03

to

Will your solution work when the original number is 0? The sum of the digits is 0, but you
return 9. But maybe the solution is "undefined" for n = 0.

On Mon, 16 Jun 2003 00:22:25 -0400, "Dana DeLouis" <ng_...@hotmail.com> wrote:

Harlan Grove

unread,

Jun 16, 2003, 5:36:30 PM6/16/03

to

"Kevin" wrote...

Life is too short for all the text processing answers you've received so far. If
you need to do this recursively until you get a single decimal digit, and if
you're always starting with positive integers, it's *ALWAYS* going to be

=1+MOD(X-1,9)

If zero is a possible starting value, modify this to

=IF(X>0,1+MOD(X-1,9),0)

Sheesh, you'd think no one remembers their Number Theory.

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something else.

Peo Sjoblom

unread,

Jun 16, 2003, 9:48:24 PM6/16/03

to

Nice..

--

Regards,

Peo Sjoblom

"Harlan Grove" <hrl...@aol.com> wrote in message
news:yLqHa.488$cJ5...@www.newsranger.com...

Kevin

unread,

Jun 26, 2003, 9:04:54 AM6/26/03

to

Elegant solution Harlan!

Thanks all- I learned a lot here.

>.
>

Myrna Larson

unread,

Jul 15, 2003, 9:46:50 AM7/15/03

to

Or maybe (like me) they never had that course <g>.