Lesson 6: Calculate at full precision, round at the right step
Excel stores many decimal fractions as binary approximations. Keep precision through intermediate calculations when possible, then round for display or when a business rule requires rounding each transaction.
With A2 = -2.35, =ROUND(A2,1) returns -2.4; =ROUNDDOWN(A2,1) returns -2.3; =INT(A2) returns -3.
Math and rounding formula reference
31 formulas shown
| Function | Purpose | Example and result |
|---|---|---|
| SUMPRODUCT | Multiplies corresponding array items and sums them. | =SUMPRODUCT(B2:B4,C2:C4) totals quantity times price. |
| ABS | Absolute value (distance from zero). | =ABS(-12) returns 12. |
| SIGN | Returns -1, 0, or 1 based on sign. | =SIGN(-12) returns -1. |
| MOD | Returns the remainder after division. | =MOD(17,5) returns 2. |
| QUOTIENT | Returns the integer portion of a division. | =QUOTIENT(17,5) returns 3. |
| INT | Rounds down to the nearest integer. | =INT(-2.3) returns -3. |
| TRUNC | Removes digits without rounding. | =TRUNC(12.89,1) returns 12.8. |
| ROUND | Rounds to a chosen number of digits. | =ROUND(12.345,2) returns 12.35. |
| ROUNDUP | Rounds away from zero. | =ROUNDUP(12.341,2) returns 12.35. |
| ROUNDDOWN | Rounds toward zero. | =ROUNDDOWN(-12.349,2) returns -12.34. |
| MROUND | Rounds to a multiple. | =MROUND(17,5) returns 15. |
| CEILING.MATH | Rounds up to a multiple using math rules. | =CEILING.MATH(17,5) returns 20. |
| FLOOR.MATH | Rounds down to a multiple using math rules. | =FLOOR.MATH(17,5) returns 15. |
| EVEN / ODD | Rounds away from zero to the next even or odd integer. | =EVEN(3) returns 4; =ODD(4) returns 5. |
| POWER | Raises a number to a power. | =POWER(3,4) returns 81. |
| SQRT | Returns the positive square root. | =SQRT(81) returns 9. |
| EXP | Returns e raised to a power. | =EXP(1) returns approximately 2.71828. |
| LN / LOG / LOG10 | Natural, chosen-base, and base-10 logarithms. | =LOG(1000,10) returns 3. |
| PI | Returns pi to Excel's stored precision. | =PI()*2 calculates a circle circumference when multiplied by radius. |
| RAND | Random decimal from 0 up to but not including 1. | =RAND() recalculates. |
| RANDBETWEEN | Random integer in an inclusive range. | =RANDBETWEEN(1,6) simulates a die. |
| FACT | Factorial of a nonnegative integer. | =FACT(5) returns 120. |
| COMBIN / COMBINA | Counts combinations with or without repeated selections. | =COMBIN(10,3) returns 120. |
| PERMUT / PERMUTATIONA | Counts ordered selections without or with repetition. | =PERMUT(10,3) returns 720. |
| GCD / LCM | Greatest common divisor and least common multiple. | =LCM(4,6) returns 12. |
| PRODUCT | Multiplies arguments. | =PRODUCT(2,3,4) returns 24. |
| SUMSQ | Returns the sum of squared arguments. | =SUMSQ(3,4) returns 25. |
| SUMX2MY2 | Sums differences between corresponding squares. | =SUMX2MY2(A2:A4,B2:B4). |
| SUMX2PY2 | Sums sums of corresponding squares. | =SUMX2PY2(A2:A4,B2:B4). |
| SUMXMY2 | Sums squared differences between array pairs. | =SUMXMY2(A2:A4,B2:B4). |
Round a unit price to cents: =ROUND(A2*B2,2)
Choose the intended rounding rule
Cell number formatting changes what is displayed, not the stored value. ROUND changes the formula result. For tax, cash, or invoice rules, confirm whether rounding occurs per line or only on the final total; those policies can produce different totals.
Progress stays in this browser.