Numerology Challenge

[bigmacfann]

Hello, I have a HUGE list of either 2 digit or 3 digit numbers in column I. I'm trying to set up a macro that would convert this whole column into a single digit number by using the numerology method. This is it in a nutshell: If cell I1 was to have '157' in it, I would like cell J1 to contain '13' (created by adding each digit in I1: 1+5+7), then in cell J1 I would like it to contain '4' (created by adding each digit in J1: 1+3). I know a macro can do this quickly but I have very little programming experience and that experience stops at QBasic. Thank you for your help in advance. Here are some more examples of how to get a single digit number by using numerology if my above example wasn't good enough:
78 -> 15 -> 6
115 -> 7
100 -> 1
899 -> 26 -> 8
177 -> 15 -> 6
204 -> 6

Thanks again.

 

[pgc01]

Hi Yogi

I believe your formula doesn't work for multiples of 9, except 9 itself. Hotpepper's, however, does work for 9.
If the zero is needed I'd add it to Hotpepper's formula as an exception:

=IF(I1=0,0,MOD(I1-1,9)+1)

or

=(MOD(I1-1,9)+1)*(I1<>0)

 

[Yogi Anand]

Ah ... Thanks pgc01!