• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing up 4 numbers till I get a single digit?

shaikhrulez

shaikhrulez

Active Member
Hello everyone,

Looking for a formula which can sum 4 digit numbers till left with single digit.

For example: when I enter 4596 in cell A1

It should add like this, 4+5+9+6 = 24

then again add the result like this, 2 + 4 = 6

Thanks.
 
Khalid NGO said:
Hello Waqar,
Another could be:

in B1 with Ctrl+Shift+Enter:
=SUM(IFERROR(--MID(A1,ROW($1:$99),1),0))

Copy to right.

Regards,
Click to expand...

Thanks Khalid, I think there is something missing in your formula. Try these numbers 8975, 1098, 9874. It is giving results in 2 digits & I'm looking for a single digit.

I've found 2 formulas in the link shared by @Luke M giving the desired results.

=IF(MOD(A1,9)=0,9,MOD(A1,9))

Another shorter one is
=1+MOD(A1-1,9)

Anyways, thanks everyone for the input :)
 
shaikhrulez said:
Thanks Khalid, I think there is something missing in your formula. Try these numbers 8975, 1098, 9874. It is giving results in 2 digits & I'm looking for a single digit.

I've found 2 formulas in the link shared by @Luke M giving the desired results.

=IF(MOD(A1,9)=0,9,MOD(A1,9))

Another shorter one is
=1+MOD(A1-1,9)

Anyways, thanks everyone for the input :)
Click to expand...
Oh, after entering formula in B1, you need to copy it right side 2 times (i.e. till column D)

for 9875
you will get 29 with above formula in COLUMN B
copy again will give you 11 in COLUMN C
copy again will produce the final 2 in COLUMN D

But I suspect MOD version is much shorter and without helper columns.

Regards,
 
Khalid NGO said:
Oh, after entering formula in B1, you need to copy it right side 2 times (i.e. till column D)

for 9875
you will get 29 with above formula in COLUMN B
copy again will give you 11 in COLUMN C
copy again will produce the final 2 in COLUMN D

But I suspect MOD version is much shorter and without helper columns.

Regards,
Click to expand...

Yeah I was looking for simple & short formula without using any helper columns; & found exactly that here.

BTW, I've been using this long formula since long time.. :p

=MID(A1,1,1)+IF(MID(A1,2,1)="",0,MID(A1,2,1))+IF(MID(A1,3,1)="",0,MID(A1,3,1))+IF(MID(A1,4,1)="",0,MID(A1,4,1))

=IFERROR(MID(B1,1,1)+MID(B1,2,1),B1)

=IFERROR(MID(C1,1,1)+MID(C1,2,1),C1)
 
shaikhrulez said:
Yeah I was looking for simple & short formula without using any helper columns; & found exactly that here.

BTW, I've been using this long formula since long time.. :p

=MID(A1,1,1)+IF(MID(A1,2,1)="",0,MID(A1,2,1))+IF(MID(A1,3,1)="",0,MID(A1,3,1))+IF(MID(A1,4,1)="",0,MID(A1,4,1))

=IFERROR(MID(B1,1,1)+MID(B1,2,1),B1)

=IFERROR(MID(C1,1,1)+MID(C1,2,1),C1)
Click to expand...
:) that's bit long
will cover only 4 digit.

one more thing.
if you have space between numbers like 9 875 or 9 8 7 5
or decimal 9.875 or 9.8.7.5

all above formulas (except i posted) will produce error / wrong results.
Dont worry if you have fixed real numbers.
 
You must log in or register to reply here.
Back
Top