Rounding formula

[Nathalie_Salas]

Hi there

I have this formula below where I need to also round up by 5 or round down by 5 ( e.g. 4556, to 45560 or 2333 to 2335)

({Original Price AED} * (100 - Discount)) / 100

Could anyone please advise how I integrate the round formula within the above formula?

Thanks,

Nathalie

[Andre_Zijlstra]

That formula would be:
ROUND([NUMBER],-1)

[Nathalie_Salas]

HI Andre

Many thanks. How can I include that formula within my existing formula?

({Original Price AED} * (100 - Discount)) / 100

Or do I have to create a separate column to do the ROUND formula?

[W_Vann_Hall · Accepted solution]

I think there’s a typo in your original example. If by

round up by 5 or round down by 5

you mean round to the nearest multiple of 5, where 4556 would round to 4555, 2333 to 2335, and 2332 to 2330, the formula you want is

ROUND(number*2,-1)/2

To incorporate that into your current formula, just replace 'number' with your formula:

ROUND((({Original Price AED} * (100 - Discount)) / 100)*2,-1)/2

[Jamie_Whoolery]

I can see that it works, but I don’t quite understand how it works so I’m having trouble modifying this formula to round to a multiple of 15. Is this possible? I’m pulling the minute from a date_time field and need to round to the nearest quarter hour.

Thanks,
Jamie

[W_Vann_Hall]

Try

ROUND(nbr*.67,-1)/.67

That seems to work, but play with it a bit to make sure.

Note: Anything 52.5 minutes or longer is rounded up to 60 minutes — which you probably don’t want. Here are two variations:

If nbr is number of minutes, possibly > 60

IF(
    ROUND(ROUND(MOD(nbr,60)*.67,-1)/.67,0)=60,
    INT(nbr/60)+1&':00',
    INT(nbr/60)&':'&ROUND(ROUND(MOD(nbr,60)*.67,-1)/.67,0)
    )

If you’ve already split a time field into {hr} (hours) and {min} (minutes)

IF(
    ROUND(ROUND(min*.67,-1)/.67,0)=60,
    hr+1&':00',
    hr&':'&ROUND(ROUND(min*.67,-1)/.67,0)
    )