That formula would be:
ROUND([number],-1)
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?
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 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
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)
)
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