I want to be able to either round up or down to the nearest 5p.

rounding up example :

If Price was £2.67 = £2.70

If Price was £2.63 = £2.65

If Price was £4.96 = £5.00

Rounding down example :

If Price was £2.67 = £2.65

If Price was £2.63 = £2.60

If Price was £4.96 = £4.95

I have tried rounded to, ceiling and floor but cant seem to get it to work. Any suggestions?

Well this should round down to 0.65 surely?

And this should round to £4.96?

To round to the nearest 5p:

`Price / 5 :rounded to two decimal places * 5`

No I need it to round to the nearest 5p.

Basically i have designed a pour cost calculator used by bars to work out their drinks pricing it takes the bottle price, bottle size, serving size etc and works out what the recommended sales price should be based on the pour cost percentage (basically an inverse of margin).

most bars would round to the nearest 5p so if my pour cost calculator said you should sell at £4.67 as a bar you wouldn’t stick £4.67 on the menu price list you would opt to round down to £4.65 or up to £4.70.

i will give my users the option if they want to round up all of their prices, or round them down. But i cant seem to get the calculations to work correctly.

I have managed to solve this problem if anyone is looking for the solution this is it :

Lets take £2.67 as an example and we want to round up to the nearest 5p.

2.67 / 0.05 :ceiling * 0.05

To round down use floor instead of ceiling.

Bless you both for having this discussion. I’m in the US and frequently get requests for our software to round up or round down to the nearest quarter ($0.25). Looks like you two have given me a solution with an added bonus of being able to control whether it consistently rounds up or down if that gets requested by our users - thank you!

3 Likes

Yep, I didn’t think about doing it the other way (dividing rather than multiplying) so good idea from OP.

The general formula for anyone stumbling across this is as follows:

where number = the number to round

rounding interval = the rounding intervals (e.g for nearest 5 use 5, for nearest quarter use 0.25)

Number / rounding interval :(rounded to 0 decimal places / floor / ceiling) * rounding interval

Use rounded to 0 to round to the nearest. Use floor to round down. Use ceiling to round up.

1 Like