In Australia we did away with 1 and 2 cent coins years ago. So all prices, where paid in cash, are rounded down to the nearest value divisible by 5 cents.
In Excel we can use the FLOOR function to calculate this value. For example:
Say our price is $4.32 and we need to round it down to the nearest value divisible by 5 cents, the FLOOR function would read:
=FLOOR(4.32, 0.05)
= $4.30
On the flip side we could use the CEILING function to round our price up to the nearest 5 cents as follows:
=CEILING(4.32, 0.05)
= $4.35
How do the FLOOR and CEILING Functions Work?
The syntax is:
=FLOOR(number, significance)
=CEILING(number, significance)
Where the number is your starting point and the significance is the multiple you want your number rounded down to for FLOOR, or up to for CEILING.
Unlike ROUNDUP or ROUNDDOWN, Excel’s FLOOR and CEILING functions can round the decimal places of a value to be divisible by a number you specify.
FLOOR Function Cool Trick
You’ve probably noticed that ‘7’ is the new ‘9’; with prices like $9.97 now in place of $9.99, and $9.47 in place of $9.49. So what if you wanted to round your prices to end in 47 cents or 97 cents?
We can use a combination of the FLOOR function and an IF statement to achieve this.
In row 25 in the example below you can see in the formula bar we have used the following formula:
=FLOOR(D25,1)+IF(CEILING(D25,1)-(D25)<0.5,0.97,0.47)
Taking row 25 as an example let me break this formula down and explain it in English:
1. The first part =FLOOR(D25,1) simply gets rid of the decimal places so you’re left with $4.00.
2. The second part IF(CEILING(D25,1)-(D25)<0.5,0.97,0.47) works out if the cents are less than 50, and if they are round them down to 47 cents, otherwise round them up to 97 cents.
3. The plus sign in between the first FLOOR function and the IF Statement adds the two results together giving the result $4.47.
