Formula to subtract from nearest tens

[Kalie]

Hello,

I have a formula that my employees use (and have problems with) frequently that I am trying to formulate an excel spreadsheet for to remove the human error. Most of the formula is very straight-forward, but the last part is causing me issue. So the formula spits out a number and you have to subtract that number from the nearest 10's number and the difference goes into your final calculation. For Example, my formula spits out 87. The next 10's is 90, so 90-87=3, 3 is the number that I need to complete my answer. Is there any way to program this into excel? My employees will be entering a 7 or 8 digit number, and need to come out with an 8 or 9 digit number (respectively) and I would like excel to be able to tell them the whole finished number without them having to do that math separately.

Thank you!

 

[Michael M]

Can you post the formula you are currently using ??

 

[joeu2004]

[.... deleted by me ....]

 

[joeu2004]

[Sorry about my previous response. It was incomplete; and I misunderstood the "complete answer" that you require.]

have to subtract that number from the nearest 10's number and the difference goes into your final calculation. For Example, my formula spits out 87. The next 10's is 90, so 90-87=3, 3 is the number that I need to complete my answer. [....] My employees will be entering a 7 or 8 digit number, and need to come out with an 8 or 9 digit number (respectively)

Do you really mean nearest multiple of 10 greater than the number? If the number is 83, do you want 7 (90-83) or 3 (83-80)?

1. Nearest greater than:

=A1*10 + ROUNDUP(A1,-1) - A1

2. Nearest below or above:

=A1*10 + ABS(ROUND(A1,-1) - A1)

Those formulas "append" the check digit (difference between the original number in A1 and a multiple of 10), creating a number one digit longer.