Addition and subtraction with abbreviated numbers

**sergiu_gh** · 01-06-2015, 07:08 AM

Hello everybody. First of all, sorry for my english.
I'm having an issue with a result given from a simple subtraction formula.
The best way to be understood is to give an example:
I have a number: 1300.
I need to calculate some percent values, from 1300: 10.5%, 5.5% and 0.5%.
The results should be: 136.5, 71.5 and 6.5. I need to have numbers without decimals, so I will consider 137, 72 and 7.
Now, I made formulas, as: =10.5%*B2 (where B2 is number 1300). So I get 136.5. By formatting the result cell with no decimals, Excel will give me the abbreviated number I need: 137. Also, I get 72 and 7 as well.

But if I try to find out the result given by the subtraction of 137, 72 and 7, from 1300, using a formula, as: =B2-B4-B5-B6, where B2 is 1300, and B4,B5 and B6 are the results with no decimals made with the first formula I presented above, I get 1086, instead of 1084. Excel is using the 136.5, 71.5 and 6.5 numbers, even if it shows me the abbreviated numbers. I need Excel to use the abbreviated numbers, so I can get 1084 number.
Is there a specific function for that? Hope you understood.
Thank you!

**udprocks** · 01-06-2015, 07:15 AM

Hallo Sergiu,

Welcome to the forum,

ok so just try below formula for getting 137, 72 & 7
if 136.5 is in a2 then write a formula in b2

=ceiling(a2,1)

and then try your calculations.

see the file.

hope it helps you.

**tchock** · 01-06-2015, 07:52 AM

Salut Sergiu,

another solution would be to use ROUND. In your example: =ROUND(10.5%*B2,0).

**sergiu_gh** · 01-06-2015, 07:54 AM

Hello, udprocks!
Thank you very much for the solution. It works!

Salut, tchock!
Thank you for this one! Actually it's the best one, because you can skip additional formulas. So everything is in one cell.

Thanks both of you, again!

Have a nice day!