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!

PRYOR+ 7-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small> Basics

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

May. 24

Eastern Time Zone

May. 25

Central Time Zone

Jun. 1

Central Time Zone

Jun. 6

Eastern Time Zone

Jun. 7

Central Time Zone

Jun. 9

Pacific Time Zone

Jun. 13

Central Time Zone

Jun. 15

Central Time Zone

Jun. 16

Eastern Time Zone

Jun. 20

Central Time Zone

Jun. 21

Pacific Time Zone

Jun. 23

Eastern Time Zone

Jun. 28

Eastern Time Zone

Jul. 6

Pacific Time Zone

Jul. 7

Central Time Zone

Jul. 11

Eastern Time Zone

Jul. 12

Central Time Zone

Jul. 14

Central Time Zone

Jul. 18

Pacific Time Zone

Jul. 20

Central Time Zone

Jul. 21

Eastern Time Zone

Jul. 25

Eastern Time Zone

Jul. 26

Central Time Zone

Jul. 27

Central Time Zone

Aug. 1

Pacific Time Zone

Aug. 3

Central Time Zone

Aug. 4

Eastern Time Zone

Aug. 9

Central Time Zone

Aug. 11

Central Time Zone

Aug. 15

Eastern Time Zone

Aug. 16

Central Time Zone

Aug. 18

Pacific Time Zone

Aug. 23

Central Time Zone

Aug. 25

Pacific Time Zone

Aug. 29

Eastern Time Zone

Aug. 31

Central Time Zone

Sep. 7

Central Time Zone

Sep. 12

Central Time Zone

Sep. 13

Pacific Time Zone

Sep. 15

Eastern Time Zone

Sep. 19

Central Time Zone

Sep. 21

Central Time Zone

Sep. 22

Eastern Time Zone

Sep. 26

Pacific Time Zone

Sep. 27

Central Time Zone

Sep. 29

Central Time Zone

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>: Beyond the Basics

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

May. 25

Eastern Time Zone

May. 26

Central Time Zone

Jun. 2

Central Time Zone

Jun. 7

Eastern Time Zone

Jun. 8

Central Time Zone

Jun. 10

Pacific Time Zone

Jun. 14

Central Time Zone

Jun. 16

Central Time Zone

Jun. 17

Eastern Time Zone

Jun. 21

Central Time Zone

Jun. 22

Pacific Time Zone

Jun. 24

Eastern Time Zone

Jun. 29

Eastern Time Zone

Jul. 7

Pacific Time Zone

Jul. 8

Central Time Zone

Jul. 12

Eastern Time Zone

Jul. 13

Central Time Zone

Jul. 15

Central Time Zone

Jul. 19

Pacific Time Zone

Jul. 21

Central Time Zone

Jul. 22

Eastern Time Zone

Jul. 26

Eastern Time Zone

Jul. 27

Central Time Zone

Jul. 28

Central Time Zone

Aug. 2

Pacific Time Zone

Aug. 4

Central Time Zone

Aug. 5

Eastern Time Zone

Aug. 10

Central Time Zone

Aug. 12

Central Time Zone

Aug. 16

Eastern Time Zone

Aug. 17

Central Time Zone

Aug. 19

Pacific Time Zone

Aug. 24

Central Time Zone

Aug. 26

Pacific Time Zone

Aug. 30

Eastern Time Zone

Sep. 1

Central Time Zone

Sep. 8

Central Time Zone

Sep. 13

Central Time Zone

Sep. 14

Pacific Time Zone

Sep. 16

Eastern Time Zone

Sep. 20

Central Time Zone

Sep. 22

Central Time Zone

Sep. 23

Eastern Time Zone

Sep. 27

Pacific Time Zone

Sep. 28

Central Time Zone

Sep. 30

Central Time Zone

Advanced Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>-Macros, PivotTables, Charts and More

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

LIVE ONLINE SEMINARS

May. 23

Central Time Zone

Jun. 3

Central Time Zone

Jun. 8

Eastern Time Zone

Jun. 14

Eastern Time Zone

Jun. 16

Central Time Zone

Jun. 22

Central Time Zone

Jun. 23

Pacific Time Zone

Jun. 27

Eastern Time Zone

Jul. 6

Central Time Zone

Jul. 12

Pacific Time Zone

Jul. 13

Eastern Time Zone

Jul. 18

Central Time Zone

Jul. 19

Eastern Time Zone

Jul. 28

Eastern Time Zone

Jul. 29

Central Time Zone

Aug. 2

Eastern Time Zone

Aug. 9

Pacific Time Zone

Aug. 12

Central Time Zone

Aug. 17

Eastern Time Zone

Aug. 18

Central Time Zone

Aug. 22

Central Time Zone

Aug. 25

Eastern Time Zone

Aug. 31

Pacific Time Zone

Sep. 9

Central Time Zone

Sep. 14

Eastern Time Zone

Sep. 15

Pacific Time Zone

Sep. 19

Central Time Zone

Sep. 27

Eastern Time Zone

Sep. 28

Central Time Zone

Related Advanced Excel Articles

PRYOR+ 7-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.

Categories