Ravindra BagaleCourses & study guides Track your progress

Guides

ROUND, ROUNDUP, ROUNDDOWN, MROUND and CEILING: Which Rounding to Use

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Raja's invoice shows ₹1,234.57 but the total at the bottom is one paisa off from the sum of the lines. The cells were only formatted to 2 decimals; the hidden values still had more decimals. He also needs to know how many cartons of 12 he needs for 250 items: 20.83 must become 21, not 20.

Number formatting only changes how a value looks. ROUND changes the value itself. Use ROUND for normal rounding, ROUNDUP or CEILING.MATH when you must never go below (cartons, seats), ROUNDDOWN or FLOOR.MATH when you must never go above, and MROUND to round to the nearest multiple like ₹5.

Download the practice file (round-roundup-mround.xlsx)

Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Normal rounding to 2 decimals: =ROUND(A2,2).
  2. To the nearest ₹100: =ROUND(A2,-2) (negative digits round to the left of the decimal point).
  3. Always up: =ROUNDUP(A2,0); for cartons =ROUNDUP(250/12,0) gives 21.
  4. Nearest multiple of 5: =MROUND(A2,5).
  5. Wrap the calculation itself: =ROUND(B2*C2*18%,2), not just the final total.

Ten examples with 1234.567

WhatFormulaResult
Round to 2 decimals=ROUND(1234.567,2)1234.57
Round to nearest 100=ROUND(1234.567,-2)1200
Always up to a whole number=ROUNDUP(1234.567,0)1235
Always down to a whole number=ROUNDDOWN(1234.567,0)1234
Nearest multiple of 5=MROUND(1234.567,5)1235
Up to the next 10=CEILING.MATH(1234.567,10)1240
Down to the previous 10=FLOOR.MATH(1234.567,10)1230
Cartons for 250 items, 12 per carton=ROUNDUP(250/12,0)21
INT of -2.5 (goes down)=INT(-2.5)-3
TRUNC of -2.5 (cuts decimals)=TRUNC(-2.5)-2

Format vs ROUND

  1. Type =10/3 in a cell. Format it with 2 decimals: it shows 3.33.
  2. Multiply it by 3 in another cell: the answer is 10, not 9.99, because the real value is 3.3333...
  3. Use =ROUND(10/3,2)*3 and the answer is 9.99.
  4. Rule: if the rounded number is printed on a bill and then added up, round each line with ROUND.

Which function when

NeedFunction
Normal rounding (5 and above goes up)ROUND
Never short: cartons, buses, seatsROUNDUP or CEILING.MATH
Never over: full boxes you can ship, complete weeksROUNDDOWN or FLOOR.MATH
Price ending in 0 or 5MROUND(price,5)
Remove decimals without roundingTRUNC
Whole part of a positive numberINT

INT and TRUNC give the same answer for positive numbers. For negative numbers INT goes down (INT(-2.5) = -3) and TRUNC just cuts (-2).

Ravindra Bagale's Tip

On GST bills, round the tax of each line with ROUND(...,2) before you add the lines. Rounding only the grand total is how a register ends up one rupee different from the invoices.

Practice task

  1. Open the Rounding sheet and compare each formula in column B with its result in column C.
  2. Change the carton size in the formula to 10 and check the answer.
  3. In E2 type =10/3 and format it to 2 decimals. In E3 type =E2*3. Explain the result.
  4. In E4 type =ROUND(10/3,2)*3.
  5. Round 1234.567 to the nearest 50 with MROUND.

Learn it properly

Course lessons:

Related guides: Percentage Formulas in Excel: Growth, Share, Discount, Margin and GST · GST Calculation in Excel: Add GST, Remove GST and Split CGST/SGST · PMT, IPMT and PPMT: Calculate EMI and Build a Loan Schedule

Frequently asked questions

What is the difference between ROUND and ROUNDUP?

ROUND goes to the nearest value (2.4 becomes 2, 2.5 becomes 3). ROUNDUP always goes away from zero (2.1 becomes 3).

How do I round to the nearest thousand?

=ROUND(A2,-3). 1,23,456 becomes 1,23,000.

Why does MROUND give #NUM!?

The number and the multiple have different signs, for example =MROUND(-12,5). Use the same sign for both.

Is CEILING.MATH available in Excel 2010?

No. It came in Excel 2013. Use =CEILING(A2,10) in older versions.