4 Formulas to Round Off Prices To Nearest 10 Cents in Excel
There are a few rounding functions in Excel, depending on what kind of values you want to round and how. Each rounding function rounds a number to certain decimal places depending upon the arguments. In this article, I will describe 4 formulas to round off prices to the nearest 10 cents in Excel.
In the column, Vegetables, I have a list of some vegetables. In the column, Prices, I have mentioned the prices of each vegetable. I have added another column, Nearest 10 Cents next to these two columns for calculating the round-off prices to the nearest multiple of 10 cents.
Round Off Prices to Nearest 10 Cents in Excel Using MROUND Function
The easiest and most reliable way to round off a number to its nearest multiple is to use the MROUND function. Because the MROUND function is only dedicated to rounding off the numbers to the nearest multiple. The multiple is sometimes either an integer or a fraction.
Syntax
=MROUND (number, multiple)Formula
=MROUND(B3,0.1)Formula Explanation
As specified by the syntax of the MROUND function, B2 indicates the number in the formula. And 0.1 is multiple. The multiple determines that the formula has to return the nearest multiple of 0.1 of the mentioned number i.e. $40.73.
If you change the value 0.1 and mention something else, for example, 0.2, the answer will be changed. It will return the nearest multiple of 0.2 of $40.73.
To round off prices nearest 10 cents in Excel with the MROUND function, here are the steps below:
- First, type the formula in cell C2.
- Then, press ENTER.
- After that copy the formula down to cell C8 by dragging the right-bottom corner of cell C2.
You can see, that the MROUND function rounded off all the vegetable prices to their nearest multiple of 10 cents.
Like, in the case of the first item, the mushroom, the price is $40.73. There are two nearest multiples of 10 cents of the price of $40.73. One is $40.70, and the other is $40.80. From these two prices, the $40.70 is closer to $40.73 than $40.80.
As a result, the formula returned the value of $40.70 in cell C2 instead of $40.80.
- [3 Ways] Round Off Time to Nearest Multiple of 5 Minutes in Excel
- 2 Ways to Round to Nearest 50 Cents in Excel
Round Off Prices to Nearest 10 Cents with ROUND Function
Another way to round off prices to their nearest 10 cents is using the ROUND function in Excel.
Syntax
=ROUND (number, num_digits)Formula
=ROUND(B2,1) Formula Explanation
From the result, you can see, that B2 represents the number argument of the ROUND function. It refers to the value of $40.73. Then 1 is the num_digit argument.
Basically, 1 describes that there will be only 1 digit after the decimal point after rounding off the numbers.
Use the ROUND function to round off the nearest 10 cents in Excel. So, here’s how:
- First, select a cell.
- Insert the formula: =ROUND(B2,1)
- Press the ENTER key.
- Finally, copy the formula down to cell C8 by dragging the right-bottom corner of cell C2.
The ROUND function worked great and rounded off all the prices to the nearest multiple of 10 cents of all values of column B.
Round Off Prices to Nearest Previous 10 Cents by FLOOR Function
An alternative way to round off the prices to their nearest 10 cents is by using the FLOOR function. But the FLOOR function only returns the exact previous multiple of any value. In that case, you have to keep in mind this fact.
Syntax
=FLOOR(number, significance)Formula
=FLOOR(B3,0.1)Formula Explanation
According to the syntax of the FLOOR function, B2 is the number argument and 0.1 is the significance argument of the syntax. Both of the arguments are required for the FLOOR function. The significance, 0.1, indicates that the result must be the previous multiple of 10 cents of cell B2 ($40.73).
Apply the FLOOR function to round off prices to the nearest 10 cents. Now, go through the steps below:
- First, click on a blank cell.
- Write down the formula in cell C2.
- Then press ENTER.
- Finally, copy the formula down by dragging the right-bottom corner of cell C2.
Take a look at cell B7 and its result in cell C7. The closest multiple of 10 cents of $71.96 is $71.90 and $72.00. But comparing both of the values, the result is $71.90, even though $72.00 was the closer one. It’s because the FLOOR function rounds the numbers down to their previous multiple of any value.
Round Off Prices to Nearest Next 10 Cents Using CEILING Function
The CEILING function is technically the opposite of the FLOOR function. That means the CEILING function rounds the values in an upward trend. So, if you use the CEILING function it will round off prices to the nearest next multiple of 10 cents of a value.
Syntax
=CEILING (number, significance)Formula
=CEILING(B2,0.1)Formula Explanation
Here, according to the formula syntax, B2 is the number, and 0.1 is the significance. For the CEILING function, both arguments are required.
According to the significance, the result must be the larger one of the multiple of 10 cents of $40.73.
To round off the nearest next 10 cents in Excel with the CEILING function, follow these steps below:
- Firstly, select an empty cell.
- Copy and paste the formula into the selected cell: =CEILING(B2,0.1)
- Then press ENTER to insert the formula.
- Now, double-click the Fill Handle on cell C2.
You can see how the CEILING function rounded up a multiple of 10 cents of all the values.
The value in cell B2, $40.73 has become $40.80 after applying the CEILING function. If you look at the other values of column C, all the values changed in the same trend.
Conclusion
I’ve covered 4 ways to round off prices to the nearest 10 cents in Excel. I hope you find this article helpful. And, feel free to ask anything regarding this topic in the comment section below. Also, please visit our Blog page for more Excel-related articles like this one. Thank you and enjoy your day!
Frequently Asked Questions
How do I round cents?
To round cents in Excel, use the ROUND function. For example, to round to the nearest cent, use the formula: =ROUND(A1, 2), where A1 is the cell containing the numerical value. Adjust the second argument (2 in this case) to specify the desired number of decimal places for rounding.
Does Excel round 0.5 up or down?
Excel rounds 0.5 up. When applying standard rounding rules, 0.5 is rounded up to the nearest whole number in Excel.
How do you use cents in Excel?
To use cents in Excel, format cells as currency and adjust decimal places. Enter values as decimals (e.g., $5.25). To round cents, employ the ROUND function like =ROUND(A1, 2) for two decimal places, where A1 is the cell with the value.
Related Articles
- How to Round Off Numbers in Excel (9 Methods)
- 3 Formulas for Rounding Time to the Nearest Hour in Excel
- [3 Formulas] Round Off Time to Nearest Multiple of 15 Minutes in Excel
- 5 Ways to Round a Formula with SUM Function in Excel
- 2 Ways to Stop Excel from Rounding up Percentages
- How to Stop Excel from Rounding 16-Digit Numbers (2 Methods)
