Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 93 ยท Formulas Fundamentals

Percentages, Rounding and Number Precision

Calculate percentages in Excel, round with ROUND, ROUNDUP, MROUND, CEILING, FLOOR, INT and TRUNC, and understand floating-point surprises and precision as displayed.

Percentages and rounding look easy and cause a surprising share of spreadsheet mistakes. A total that is off by one cent, a column of percentages that adds up to 101%, a "50%" cell that behaves like 0.5: all of them come from the same few ideas. This lesson covers how Excel treats percentages, the family of rounding functions, and why computers sometimes give answers like 0.30000000000000004.

A reminder about this site: Excel cannot run on this page. The formulas are real Excel, and the live examples are stand-ins written in JavaScript and Python that follow the same rules (for example, rounding half away from zero). They show the idea, not Excel's internal code.

The rounding family

ROUND Nearest value, halves go away from zero.
ROUNDUP / DOWN Always away from, or toward, zero.
MROUND Nearest multiple, like 0.05.
INT / TRUNC Drop the decimals.

Formatting changes the look. Rounding changes the value.

1 / 2

What a percentage really is

A percentage is a number divided by 100. When you type 25% into a cell, Excel stores 0.25. The Percent Style format (Ctrl+Shift+%) only displays the stored value multiplied by 100 with a % sign. That is why typing 25 in a cell that is already formatted as a percentage gives 25%, while applying Percent Style to an existing 0.25 shows 25% and applying it to 25 shows 2500%.

You can also use % inside formulas: =A2*15% is the same as =A2*0.15.

The four calculations you will use all the time:

Question Formula Example
What is 15% of A2? =A2*15% 15% of 80 is 12
Add 15% to A2 =A2*(1+15%) 80 becomes 92
What share is B2 of the total? =B2/SUM($B$2:$B$5) 20 of 80 is 25%
Percent change from A2 to B2 =(B2-A2)/A2 80 to 92 is +15%

Put the percentage rate in its own cell (say E1) and refer to it with $E$1 rather than typing 15% in every formula; see cell references. Remember to format the result cells as Percent so they show 25% instead of 0.25.

Two traps deserve a warning. Percent change divides by the old value, so going from 50 to 100 is +100%, but going from 100 back to 50 is only -50%. And percentage points are a different thing from percent: a rate that moves from 10% to 12% went up by 2 percentage points, but by 20% in relative terms.

JS Percentage formulas in plain JavaScript

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The rounding functions

Excel has a whole family, and each one answers a slightly different question. The first four take the number of digits as their second argument: positive counts digits after the decimal point, 0 means a whole number, and negative rounds to tens, hundreds and so on.

Function What it does Examples
ROUND(x, n) Nearest value; exactly half goes away from zero ROUND(2.5,0) = 3, ROUND(-2.5,0) = -3, ROUND(1234.5678,-2) = 1200
ROUNDUP(x, n) Always away from zero ROUNDUP(2.1,0) = 3, ROUNDUP(-2.1,0) = -3
ROUNDDOWN(x, n) Always toward zero ROUNDDOWN(2.9,0) = 2, ROUNDDOWN(-2.9,0) = -2
MROUND(x, m) Nearest multiple of m MROUND(11,3) = 12, MROUND(10,3) = 9
CEILING(x, m) Up to the next multiple of m CEILING(4.2,0.5) = 4.5
FLOOR(x, m) Down to the previous multiple of m FLOOR(4.2,0.5) = 4
INT(x) Down to the next lower whole number INT(2.9) = 2, INT(-2.1) = -3
TRUNC(x, [n]) Cuts the decimals, no rounding TRUNC(2.9) = 2, TRUNC(-2.1) = -2

The difference that surprises people is INT versus TRUNC for negative numbers: INT always goes down (toward negative infinity), while TRUNC just removes the decimals, moving toward zero. Both give the same answer for positive numbers. MROUND needs x and m to have the same sign, otherwise it returns #NUM!. For rounding negative numbers to a multiple, newer functions CEILING.MATH and FLOOR.MATH exist; CEILING.MATH(-4.2) gives -4 (up) and FLOOR.MATH(-4.2) gives -5 (down).

JS The rounding family side by side

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A common business use: round prices to the nearest 5 cents with =MROUND(A2, 0.05), round up the number of boxes needed with =ROUNDUP(A2/12, 0) (or CEILING(A2, 12) for whole boxes of 12), and show amounts in thousands with =ROUND(A2, -3).

Rounding is not formatting

Format a cell with two decimals and Excel shows 0.33, while the cell still holds 0.3333333.... Anything that refers to the cell uses the full value. That is usually what you want, but it can make a printed table look wrong: three rows of 0.33 displayed, and a total that shows 1.00 instead of 0.99.

The cure is to round in the formula at the point where the number becomes a final amount, such as an invoice line:

TEXT
=ROUND(B2*C2, 2)       the line amount, rounded to cents
=SUM(D2:D20)           the total of the rounded lines

Now the total equals what a reader gets by adding the visible lines.

JS Displayed values versus stored values

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Precision as displayed

Excel has an option, File โ†’ Options โ†’ Advanced โ†’ "Set precision as displayed", that permanently changes stored values to match their number format. It sounds like the cure above, but it is destructive: it truncates the underlying numbers across the whole workbook and cannot be undone. Prefer explicit ROUND in formulas, which is visible, targeted and reversible.

Floating-point surprises

Excel, like almost every program, stores numbers as binary floating-point (a 64-bit "double"). Some decimal fractions, such as 0.1, have no exact binary form, so tiny errors appear: about 15 to 17 significant digits survive. Excel displays at most 15, and it also applies some tidying, so you may see 0 in one formula and 5.55E-17 in a slightly different one for the "same" subtraction. The result is that equality tests on calculated decimals can fail. Compare with a tolerance or round first:

TEXT
=A1=B1                      risky when A1 and B1 are calculated decimals
=ROUND(A1,2)=ROUND(B1,2)    safe
=ABS(A1-B1)<0.000001        also safe

Money deserves extra care: either round each line to cents as above, or keep amounts as whole cents. Python's decimal module shows what exact decimal arithmetic looks like, and also why Python's own round() can disagree with Excel:

Python Floating point and rounding rules in Python

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The takeaway: Excel's ROUND(2.5, 0) is 3 and ROUND(-2.5, 0) is -3 (half away from zero), the rule most people learned at school. If you rebuild an Excel model in Python, use Decimal with ROUND_HALF_UP and not the built-in round to match it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A cell contains 0.25 and is formatted as General. You apply Percent Style. What is displayed and stored?

    Percent Style multiplies by 100 only for display. The stored value stays 0.25.
  2. 2. What do INT(-2.1) and TRUNC(-2.1) return?

    INT rounds down toward negative infinity (-3). TRUNC just cuts the decimals (-2).
  3. 3. Which formula rounds 1234.5678 to the nearest hundred?

    A negative digits argument rounds to the left of the decimal point. -2 means hundreds, giving 1200.
  4. 4. Why can three cells showing 0.33 add up to a displayed total of 1.00?

    Formatting changes only what you see. Round with ROUND in the formula if the total should match the visible lines.
  5. 5. What is the percent change from 100 to 50?

    (50-100)/100 = -0.5, i.e. -50%. Percent change divides by the starting value.

Summary

  • A percentage is a number divided by 100; Percent Style changes only the display. Percent change is (new-old)/old, and percentage points differ from percent.
  • ROUND sends halves away from zero; ROUNDUP/ROUNDDOWN go away from or toward zero; negative digits round to tens, hundreds, thousands.
  • MROUND, CEILING and FLOOR round to multiples; INT goes down, TRUNC cuts decimals.
  • Formatting does not round. Round in the formula where an amount becomes final; avoid "Set precision as displayed".
  • Floating-point errors are normal: compare decimals with ROUND or a tolerance, never with a bare =.

Next: IF and Nested IF.