Write a cell function that will take the number 16 and turn it into 1+6=7

[Hedy]

I am trying to figure out a way to write a function in Excel for the following scenario:

I have a row of nine single digit numbers from 0 to 9. Each number is in its own cell. Each number is multipled by either 1 or 2, so the largest two digit number would be 18.

That said, you might have a 6 in one cell and 16 in another. If you have a 6 in the cell, then its sum will show as 6. However, if you have 16 in the cell, the sum needs to be 1 + 6 = 7.

How would I write a formula for the cell containing 16 that I can just drag across for my spreadsheet?

Thanks!

[Ace_XL]

Try..

=A1-FLOOR(A1-1,9)

Compliments of DLL..

[Hedy]

Pefect - thank you!

[ELeGault]

Do you have the 1 or 2 in a separate cell as well?
Lets say you were setting up a multiplication table... and wanted the results to be as you have listed...
The following will do this and is nested with 4 If statements. If it is less than 10 it will multiply straight out, Less than 100 will give you the sum of the left and right, if it is less than 1000 it will get the sum of the left, middle and right and then finally less than 10000 will give you the sum of left, left middle, right middle, right. 10000 and up will be blank... though you can continue to add/modify that

Please Login or Register  to view this content.

Here is an Example of the above applied. Notice that it is multiplying Row 1 by Column A

Example.xlsx

Cheers

[martindwilson]

another
=MOD(A1-1,9)+1

[martindwilson]

another
=MOD(A1-1,9)+1
edit you may want to cater for 0 or blank
=IF(A1=0,0,MOD(A1-1,9)+1) or
=IFERROR(A1-FLOOR(A1-1,9),0)

[Hedy]

The last two formulas that accomodate the 0 value work great. Thank you

=IF(A1=0,0,MOD(A1-1,9)+1) or
=IFERROR(A1-FLOOR(A1-1,9),0)

[martindwilson]

thanks for the feedback,glad we could help