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 instances, however It fails to reduce compound values to single digit values consistently in other instances.
For exmaple I attempted a modified version of the formula for a multiplication chart I was creating.
The formula is as follows:
=IF(SUM(B$1*$A2)>9,SUM(B$1*$A2)-9,SUM(B$1*$A2))
the result was inconsistent as it seems the condition isn't tested again after the formula has run.
Essentially the formula was subtracting 9 from all the larger compound values but it only did it once.
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.
Any help would be greatly appreciated!
Thanks in advance!
*
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
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))
