Round and Round she goes................
I didn't realize how many different rounding functions there are. And, I assuming neither did you.
=Round, =Roundup() and =Rounddown() all round a number to a specified number of decimal places.
=Round(12,3456,1) returns a value of 12.3 since 1 decimal place was specified.
=Round(12.3456,2) return a value of 12.35 since 2 decimal places were specified.
RoundUp always rounds away from the Zero and RoundDown always rounds toward the Zero.
=Roundup(12.3456,1) returns a value of 12.4
=Rounddown(12.3456,1) returns a value of 12.3
Rounding to a Multiple
MRound(), Ceiling() and Floor() round numbers to a specified multiple.
These 3 functions can be used for rounding price points or time.
=MROUND($12.94,5) will round it to the nearest 5 increment and display as $12.95
=MRound($12.94,.025) will round to the nearest .25 increment and display as $13.00
Ceiling() and Floor() are traditionally used with dollar values. They will not round if the number is already a multiple of the number. For example, =Ceiling(3.4,1) will still display as 3.4 since 3.4 is already a multiple of 1.
Software examples that you can easily understand and immediately apply to your own business needs.
How does this work for time? EG 00:01:45 roundup to the nearest minute, = 00:02:00?
ReplyDeleteGreat question because Time is different.
ReplyDeleteWhen rounding time you need to keep in mind the number of minutes hours or in a day.
If you want to round to the nearest hour then you would use 24. So, to round 1:45 to 02:00 you would use =Round(A1*24,0)/24 assuming that 1:45 was in cell A1
If you had wanted to round a time to the nearest quarter hour you would need to keep in mind that there are 96 quarter minutes in a day (1440 minutes /15) so in that case your formula would be =Round(A1*96,0)/96.