Forum Discussion
Solved
Digital Sum Formula
I'm finding an inconsistency with a formula I often use in my excel sheets. The formula is as follows: =if(sum(a+b)>9,sum(a+b)-9,sum(a+b)) the above formula actually works quite well in certain...
*
Joshua Conci wrote:
I'm trying to craft a formula that will reduce compound values such as "56" down to a single digit value of "2" by subtracting 9 from the compound value until the resulting value is less than 9.You mean this:
=MOD(A1,9)
*
Joshua Conci wrote:
I'm trying to craft a formula that will reduce compound values such as "56" down to a single digit value of "2" by subtracting 9 from the compound value until the resulting value is less than 9.
You mean this:
=MOD(A1,9)
I actually have a further question,
While I was using the new formula I noticed any multiple of 9 was being modified to 0. Which is slightly different than what Im looking for. I would like 9 to remain 9.
Any idea how to implement that?
thanks!
Try this
=MOD(A1-1,9)+1
- That did exactly what I needed!
Thank you so much for the help!
the new modified code is as follows:
=IF(SUM(B$1*$A2)>9,MOD(B$1*$A2-1,9)+1,SUM(B$1*$A2))
Thank you!!! I never thought to use that ha!
modified my formula as follows and it works like a charm:
=IF(SUM(B$1*$A2)>9,MOD(B$1*$A2,9),SUM(B$1*$A2))
