Skip to main content
Solved

Set formula result to round to nearest .5 of currency

  • March 18, 2019
  • 3 replies
  • 68 views
P

I’m trying to work out some hire prices using a formula, where the weekly cost of item is used to calculate the daily cost of it.

I have an item that cost me £1000, and I have decided that the weekly hire price is this figure divided by 10, so £100 a week. The daily price is calculated by dividing that by 3, so £33.33. However, I want the spreadsheet to round this up to £33.50. How do I use the ROUND function to do this?

At the moment I have:
Weekly cost = {Purchase Cost}/10
Then, daily cost = {Weekly Cost}/3

For both, I want it to round to the next 50p.

Thanks!

Best answer by Justin_Barrett

The CEILING idea will work, though I discovered not long ago that there’s a bug in the operation of CEILING. After reporting this to support, I was told to use the following workaround for now:

CEILING(number/significance)*significance

Tweaking the formulas from @Hairtable, that leads to:

 CEILING(({Weekly Cost}/3) / 0.5) * 0.5
 CEILING(({Purchase Cost}/10) / 0.5) * 0.5

3 replies

H
Forum|alt.badge.img+7
  • Hairtable11
  • Known Participant
  • March 18, 2019

Try this:

CEILING((Weekly Cost/3), 0.5)
CEILING((Purchase Cost/10), 0.5)

LMK how you go!

Justin_Barrett
Forum|alt.badge.img+21

The CEILING idea will work, though I discovered not long ago that there’s a bug in the operation of CEILING. After reporting this to support, I was told to use the following workaround for now:

CEILING(number/significance)*significance

Tweaking the formulas from @Hairtable, that leads to:

 CEILING(({Weekly Cost}/3) / 0.5) * 0.5
 CEILING(({Purchase Cost}/10) / 0.5) * 0.5
H
P
  • Peter_Baumann
  • Author
  • New Participant
  • March 19, 2019

The CEILING idea will work, though I discovered not long ago that there’s a bug in the operation of CEILING. After reporting this to support, I was told to use the following workaround for now:

CEILING(number/significance)*significance

Tweaking the formulas from @Hairtable, that leads to:

 CEILING(({Weekly Cost}/3) / 0.5) * 0.5
 CEILING(({Purchase Cost}/10) / 0.5) * 0.5

Thanks, this worked great!

Terms & ConditionsAccessibility statement