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.

Round Off Prices to Nearest 10 Cents Using MROUND function


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.

Round Off Prices to Nearest 10 Cents Using the ROUND function


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.

Using FLOOR Function in Excel to Round off numbers


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.

Using CEILING Function in Excel


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!

(Visited 65 times, 2 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *