Wednesday, May 04, 2005

Rounding to nearest 5 cents in Excel

A reader sent me these two questions.

For school tuckshop - they have cost and sell prices and need a formula to work out what percentage they have added on to cost to get sell price for each item.
They need to figure out what percentage they are charging for each item – it is not uniform and they are trying to work out what they were doing so they can put correct process in place.

Can you also remind me what the "rounding up" formula is? It has been a few years since I did it and I wanted to round up to the nearest 5 cent.
Cost plus 40% rounded to nearest 5c is = ((cell name * 1.4) ROUND *.5)
Have I come anywhere near close???

The first one is actually very easy
=(sell-purchase)/purchase

The second is a little harder
Rounding to the nearest decimal point (eg 10c) is easy using the ROUND function.
=ROUND(B12*(1+markup),1)
The ROUND function uses the number of decimal places you want to round to . Using positive numbers rounds to the right of the decimal point, using 0 rounds to a whole number, and using negative numbers rounds to decimal places to the left of the decimal point.

However rounding to the nearest 5 cents is a bit more complicated.
In the following formula I have used the MOD function. This function gets the remainder after dividing by a number. In this example I have looked for all results where the remainder is 1 or 2 (any number that ends in 1, 2, 6 or 7) and then subracted the remainder from the result to get a number that ends in either .05 or .00
Wherever the remainder is 3 or 4 I have rounded up by adding the remainder
=IF(MOD(cost*(1+markup),0.05)<3,cost*(1+markup)-MOD(cost*(1+markup),0.05),cost*(1+markup)+MOD(C14,0.05))

I have attached a sample workbook. Right click and Save Target As to download.
price rounding exercise.xls


Posted by: Jethro | 7:32 AM |