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.
Introduction to the Dataset
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.
Easiest Formula to Round Off Prices to Nearest 10 Cents
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)
Usage Guide
Step_1: First, type the formula in cell C2.
Step_2: Then press ENTER.
Step_3: After that copy the formula down to cell C8 by dragging the right-bottom corner of cell C2.
Final Result
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.
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.
Alternative Formula #1: Round Off Prices to Nearest 10 Cents Using 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)
Usage Guide
Step_1: At first, type the formula in cell C2.
Step_2: Press the ENTER key.
Step_3: Finally, copy the formula down to cell C8 by dragging the right-bottom corner of cell C2.
Final Result
The ROUND function worked great and rounded off all the prices to the nearest multiple of 10 cents of all values of column B.
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.
Alternative Formula #2: Round Off Prices to Nearest Previous 10 Cents Using 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)
Usage Guide
Step_1: First, write down the formula in cell C2.
Step_2: Then press ENTER.
Step_3: Finally, copy the formula down by dragging the right-bottom corner of cell C2.
Final Result
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.
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).
Alternative Formula #3: 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)
Usage Guide
Step_1: At first, type the formula in cell C2.
Step_2: Then press ENTER to insert the formula.
Step_3: Now, double-click the Fill Handle on cell C2.
Final Result
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.
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.
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!
