The Ins and Outs of Excel Rounding Formula

There are several Excel functions that will help you round numbers to the appropriate format. You can create formulas using these functions to get your data looking the way you want it. An Excel rounding formula allows for rounding to a specific number of decimal places, the nearest even or odd number, or the nearest multiple of a certain number. The most important functions to be used when building your Excel rounding formula are ROUND and MROUND.

ROUND, ROUNDUP, and ROUNDDOWN Functions

ROUND is a basic rounding function that allows the user to round a number to the nearest value at a specified decimal place. A formula using the ROUND function is formatted as ROUND(x,y) where x is the number to be rounded and y is the number of decimals places to round to. A y value of 0 (zero) rounds the number to the nearest integer. Positive y-values round to y digits to the right of the decimal places. Negative y-values round to y digits left of the decimal place.

For example, the formula “=ROUND(123.456,-2)” results in a value of 100 while “=ROUND(123.456,2)” results in a value of 123.46.

Fred Pryor Seminars_Excel Rounding Formula_figure 1

If you want to consistently round up, substitute ROUND for the ROUNDUP function. To always round down, use the ROUNDDOWN function instead.

MROUND Function

There are instances where you may need to round to a particular multiple like 5 or 25. To do this, employ the MROUND function in your formula. This function rounds numbers to a designated multiple in the format MROUND(x,y) where x is the number to be rounded and y is multiple to round to.

Fred Pryor Seminars_Excel Rounding Formula_figure 2

MROUND can be used with the FLOOR or CEILING functions to consistently round values down (FLOOR) or up (CEILING).

ODD and EVEN Functions

There is one more set of functions that may be helpful when creating a rounding formula. The ODD and EVEN functions round a value to nearest odd or even integer, respectively.

Experienced Excel users know that there are multiple ways to produce the same results so don’t be afraid to experiment with these functions to create the Excel rounding formula that works for you!

Local Seminars Related to this Topic:

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

Oct. 22

Central Time Zone

Oct. 26

Pacific Time Zone

Oct. 28

Central Time Zone

Nov. 2

Central Time Zone

Nov. 3

Central Time Zone

Nov. 4

Eastern Time Zone

Nov. 5

Central Time Zone

Nov. 9

Central Time Zone

Nov. 10

Eastern Time Zone

Nov. 16

Pacific Time Zone

Nov. 18

Central Time Zone

Nov. 19

Central Time Zone

Nov. 30

Central Time Zone

Dec. 2

Central Time Zone

Dec. 3

Eastern Time Zone

Dec. 7

Central Time Zone

Dec. 8

Central Time Zone

Dec. 9

Eastern Time Zone

Dec. 10

Pacific Time Zone

Dec. 14

Central Time Zone

Dec. 15

Pacific Time Zone

Dec. 16

Eastern Time Zone

Dec. 17

Central Time Zone

Jan. 6

Central Time Zone

Jan. 11

Central Time Zone

Jan. 12

Pacific Time Zone

Jan. 14

Eastern Time Zone

Jan. 19

Eastern Time Zone

Jan. 20

Pacific Time Zone

Jan. 21

Central Time Zone

Jan. 25

Central Time Zone

Jan. 27

Central Time Zone

Jan. 28

Eastern Time Zone

Feb. 1

Central Time Zone

Feb. 3

Central Time Zone

Feb. 4

Eastern Time Zone

Feb. 8

Central Time Zone

Feb. 9

Eastern Time Zone

Feb. 11

Central Time Zone

Feb. 15

Central Time Zone

Feb. 17

Eastern Time Zone

Feb. 17

Central Time Zone

Feb. 23

Central Time Zone

Feb. 25

Pacific Time Zone

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

Oct. 21

Central Time Zone

Oct. 25

Pacific Time Zone

Oct. 27

Central Time Zone

Nov. 1

Central Time Zone

Nov. 2

Central Time Zone

Nov. 3

Eastern Time Zone

Nov. 4

Central Time Zone

Nov. 8

Central Time Zone

Nov. 9

Eastern Time Zone

Nov. 15

Pacific Time Zone

Nov. 17

Central Time Zone

Nov. 18

Central Time Zone

Nov. 29

Central Time Zone

Dec. 1

Central Time Zone

Dec. 2

Eastern Time Zone

Dec. 6

Central Time Zone

Dec. 7

Central Time Zone

Dec. 8

Eastern Time Zone

Dec. 9

Pacific Time Zone

Dec. 13

Central Time Zone

Dec. 14

Pacific Time Zone

Dec. 15

Eastern Time Zone

Dec. 16

Central Time Zone

Jan. 5

Central Time Zone

Jan. 10

Central Time Zone

Jan. 11

Pacific Time Zone

Jan. 13

Eastern Time Zone

Jan. 18

Eastern Time Zone

Jan. 19

Pacific Time Zone

Jan. 20

Central Time Zone

Jan. 24

Central Time Zone

Jan. 26

Central Time Zone

Jan. 27

Eastern Time Zone

Jan. 31

Central Time Zone

Feb. 2

Central Time Zone

Feb. 3

Eastern Time Zone

Feb. 7

Central Time Zone

Feb. 8

Eastern Time Zone

Feb. 10

Central Time Zone

Feb. 14

Central Time Zone

Feb. 16

Eastern Time Zone

Feb. 16

Central Time Zone

Feb. 22

Central Time Zone

Feb. 24

Pacific Time Zone

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

LIVE ONLINE SEMINARS

Oct. 21

Central Time Zone

Oct. 25

Eastern Time Zone

Oct. 29

Central Time Zone

Nov. 2

Eastern Time Zone

Nov. 8

Central Time Zone

Nov. 17

Central Time Zone

Nov. 19

Pacific Time Zone

Nov. 29

Central Time Zone

Dec. 3

Central Time Zone

Dec. 8

Central Time Zone

Dec. 9

Central Time Zone

Dec. 14

Eastern Time Zone

Dec. 15

Central Time Zone

Jan. 7

Central Time Zone

Jan. 12

Eastern Time Zone

Jan. 13

Central Time Zone

Jan. 18

Central Time Zone

Jan. 24

Central Time Zone

Jan. 26

Pacific Time Zone

Feb. 2

Eastern Time Zone

Feb. 4

Central Time Zone

Feb. 8

Pacific Time Zone

Feb. 9

Central Time Zone

Feb. 15

Eastern Time Zone

Feb. 18

Central Time Zone

Feb. 24

Central Time Zone

Related Advanced Excel Articles

Categories

Monthly Archives

Leave a Reply

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