How to calculate prices and make them end with a certain number in Excel

When you calculate prices you usually know the cost price and the desired profit percentage, so all you have to do is to add the profit to the cost price in order to calculate the sales price. However, the price list might not look as nice as you want:

EasyExcel_43__Round_Ceiling

If you want the prices to end with 9, 99, or .99, you can use the ROUND function. In E3, instead of =C3+C3*D3, we would use =ROUND(C3+C3*D3,-1)-1. The negative one (-1) inside the ROUND function means that Excel rounds up or down to the nearest power of ten, from 165.375 to 170, and the negative one in the end subtracts one from the result:
170 – 1 = 169.
We could also use ROUNDUP or ROUNDDOWN – they work in the same way as ROUND.

EasyExcel_43_2_Round_Ceiling

But what of you want all your prices rounded up to the nearest quarter or nickel? The ROUND functions can only round up or down to different multiples of 10: .1, 1, 10, 100 etc., and now we want a multiple of .05 or .25. We have to use the CEILING function: =CEILING(C3+C3*D3,0.25). The last value in the formula is the multiple you want to round up to, so if you replace 0.25 with 0.05 it will round up to the nearest nickel.

EasyExcel_43_3_Round_Ceiling

Summary

Take a look at the table below to see how the numbers change with ROUND, ROUNDUP, ROUNDDOWN, CEILING, and FLOOR

EasyExcel_43_4_Round_Ceiling

More Excel tutorials:

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish

Leave a Reply

Your email address will not be published. Required fields are marked *


six + = 11