Forum Discussion

Copper Contributor

Jun 26, 2017

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...

Formulas & Functions

tips and tricks

Detlef_Lewin
Jun 26, 2017
*
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

Copper Contributor

Jun 26, 2017

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!

SergeiBaklan

Diamond Contributor

Jun 26, 2017

Try this

=MOD(A1-1,9)+1

Joshua Conci
Copper Contributor
Jun 26, 2017
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))
- IngeborgHawighorst
  MVP
  Jun 27, 2017
  Just out of curiosity, why do you wrap a mathematical operation like a multiplication between two cells into a Sum() function?? I always wonder why people do that, so if you could care to explain, please?
  
  None of the Sum() functions does anything useful, so it can be omitted. You can just use what you put inside of the Sum() function, because it is a complete operation. Compare:
  
  =IF(SUM(B$1*$A2)>9,MOD(B$1*$A2-1,9)+1,SUM(B$1*$A2))
  is exactly the same as
  =IF(B$1*$A2>9,MOD(B$1*$A2-1,9)+1,B$1*$A2)