Root of Sum

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

I like to sum col A:C in col D which give the root of the sum.

e.g.
A1=1 B1=9 C1=9 D1=1 (The root of 1 comes from 19=1+9=10=1+0=1

Can someone show me the formula how to get the answer for col D?

Thanks
Michae
 
Michael,

Here's a simple UDF

Function RootOfSum(ParamArray rng() As Variant)
Dim tmp, newTmp
Dim i As Long
tmp = WorksheetFunction.Sum(rng)
While Len(tmp) > 1
newTmp = 0
For i = 1 To Len(CStr(tmp))
newTmp = newTmp + Mid(CStr(tmp), i, 1)
Next i
tmp = newTmp
Wend
RootOfSum = tmp
End Function

Can call with

=RootOfSum(A1:C1)
=RootOfSum(A1,B1,C1)
=RootOfSum(1,9,9)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
This is Harlan's solution. :>)

=1+MOD(SUM(A1:A3)-1,9)

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


Bob Phillips said:
Michael,

Here's a simple UDF

Function RootOfSum(ParamArray rng() As Variant)
Dim tmp, newTmp
Dim i As Long
tmp = WorksheetFunction.Sum(rng)
While Len(tmp) > 1
newTmp = 0
For i = 1 To Len(CStr(tmp))
newTmp = newTmp + Mid(CStr(tmp), i, 1)
Next i
tmp = newTmp
Wend
RootOfSum = tmp
End Function

Can call with

=RootOfSum(A1:C1)
=RootOfSum(A1,B1,C1)
=RootOfSum(1,9,9)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top