Rounding numbers is a frequently occurring need. There are plenty of use cases where you would like to round off the given number to the nearest multiple of a certain number. Let’s take the following examples and understand how to do it in Excel
- If you would like to round off the quantities of a product to the nearest multiple of 5. It can be because the product only exists in the bundles of 5
- Another example would be where you would like to compute the project completion in number of weeks when you have the estimate in number of weeks
In the first case mentioned above, we would like to have the only bundles which are complete i.e. bundles having 5 quantities of a product. In such cases, we would like to get the nearest multiple of 5 which is lesser than the actual number of quantities. Whereas in the second case we must cover all the days as leaving any day means the project can’t complete. Hence, in this case, we take a multiple of 7 which is greater than the actual number of days.
The actual excel function we use for these cases would be FLOOR for the first case and CEILING for the second case. Now, let’s look at each formula in detail.
Rounding down using the FLOOR Function:
The syntax of the FLOOR function is as follows:
The Number is the value to be rounded and Significance is the multiple to which you want to round. Floor rounds to the nearest multiple closer to 0.
- Consider the first example mentioned above and let’s say the quantities are as mentioned in the screenshot below
- Now, we want to bundle them in multiples of 5 using floor function
- We write the following formula (highlighted in the screenshot) in B2:
- Note that 24 is rounded to 20
- Drag the formula to the rest of the cells till B7. Results should look as shown in the screenshot below
This is how floor function can be used to round down a value to the multiple of a number.
Rounding up using the CEILING Function:
The syntax of the CEILING function is as follows:
The Number is the value to be rounded and Significance is the multiple to which you want to round. Ceiling rounds to the nearest multiple away from 0.
- Consider the second example mentioned above and let’s say the days for project completion are as mentioned in Column A of screenshot below
- Now, we want to calculate the weeks which would be in multiples of 7(days) using ceiling function
- You can write the following formula (highlighted in the screenshot) in B2. Also, add the formula B2/7 in C2 to calculate the duration in weeks:
- Note that 58 is rounded to 63
- Drag the formula to the rest of the cells till B6. Results should look as shown in the screenshot below
This is how ceiling function can be used to round up a value to the multiple of a number.
Unlike ROUNDUP or ROUNDDOWN function in Excel, FLOOR and CEILING functions will round the decimal places of a value to be divisible by a number you specify. Refer to the following file for the formulas of the above discussion:
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018