Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Rounding formula

Solved
Jump to Solution
11345 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Nathalie_Salas
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions

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

See Solution in Thread

6 Replies 6

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

Nathalie_Salas
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

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

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

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)
    )

What if I have a number like 1240 or 770 and I need to round it to the nearest hundred? Would we have any solution?
Thank you all