Root of Sum
 

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

--
Message posted from http://www.ExcelForum.com

Root of Sum
 

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)

"Michael168 " wrote in message
...
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
Michael


---
Message posted from http://www.ExcelForum.com/