Useful Math Formulas and Functions in Google Sheets
August 7th, 2026
You do not need a textbook to get mileage out of Sheets’ math functions. Day-to-day work needs powers and roots, absolute values, controlled rounding, remainders, and integer factors far more often than formal algebra. This post covers the functions people actually reach for — POWER, SQRT, ABS, ROUND / ROUNDUP / ROUNDDOWN, MOD, LCM, GCD, INT, and PRODUCT — with practical spreadsheet patterns.
POWER and SQRT — growth, area, and scaling
POWER raises a number to an exponent. It is interchangeable with the ^ operator, but reads more clearly inside nested formulas.
=POWER(1.05, 10)
=1.05^10
Both return about 1.6289 — a 5% annual growth factor over 10 years. For compound interest style work:
=A2*POWER(1+B2, C2)
With principal in A2, rate in B2, and periods in C2, that projects the future value under discrete compounding. For continuous growth you would use EXP and LN instead — see Understanding Logarithms: A Guide for Beginners.
SQRT is the square root — the same as POWER(x, 0.5) but clearer:
=SQRT(A2)
Practical uses: side length from area (SQRT(area)), root-mean-square style steps, and distance formulas. Example — Euclidean distance between two points (x1,y1) and (x2,y2) in A2:B2 and C2:D2:
=SQRT(POWER(C2-A2, 2) + POWER(D2-B2, 2))
ABS — magnitude without the sign
ABS strips the sign. Use it for error sizes, variances you want as positive deltas, and any “how far off” metric.
=ABS(B2-A2)
That is the absolute difference between forecast (A2) and actual (B2). Ranking biggest misses:
=ABS(B2-A2)/A2
gives absolute percentage error when A2 is the baseline. Conditional formatting rules often key off ABS so both over- and under-shoots light up the same way.
ROUND, ROUNDUP, ROUNDDOWN — control display and money
Floating-point math produces ugly tails (19.999999). Rounding functions fix currency, inventory packs, and report presentation.
ROUND uses standard half-away-from-zero style rounding to a given number of places:
=ROUND(19.955, 2)
Returns 19.96. For money columns, round once at the end of a calculation chain — not after every intermediate step — unless tax rules say otherwise.
ROUNDUP always goes away from zero; ROUNDDOWN always toward zero:
=ROUNDUP(2.1, 0)
=ROUNDDOWN(2.9, 0)
Return 3 and 2. Classic billing pattern — charge whole seats, never partial:
=ROUNDUP(A2/B2, 0)*B2
If A2 is users and B2 is pack size (for example 5), you bill in full packs only.
Rounding to nearest 5 or 0.05 often combines with division:
=ROUND(A2/5, 0)*5
=ROUND(A2/0.05, 0)*0.05
INT — whole number toward negative infinity
INT returns the integer portion by flooring toward −∞:
=INT(3.9)
=INT(-3.1)
Return 3 and -4. That differs from ROUNDDOWN for negatives (ROUNDDOWN(-3.1, 0) is -3). Prefer INT when you mean floor; prefer ROUNDDOWN when you mean “drop the decimal places toward zero.”
Extract whole days from a duration stored as a date-time serial:
=INT(B2-A2)
The fractional part is the leftover time-of-day portion — useful when mixing date math with the patterns in Working with Dates in Google Sheets.
MOD — remainders, grouping, and alternating rows
MOD returns the remainder after division:
=MOD(17, 5)
Returns 2. Everyday uses:
Alternating row shading helper (0/1 flag):
=MOD(ROW(), 2)
Bucket IDs into N groups (for A/B/C assignment):
=MOD(A2-1, 3)+1
Detect multiples — remainder zero means divisible:
=IF(MOD(A2, 4)=0, "Divisible by 4", "Not")
Time arithmetic — minutes past the hour when total minutes sit in A2:
=MOD(A2, 60)
LCM and GCD — scheduling and scaling
GCD is the greatest common divisor; LCM is the least common multiple. Both accept multiple numbers or ranges.
=GCD(24, 36, 60)
=LCM(24, 36, 60)
Return 12 and 360.
Scale a recipe — you have 24, 36, and 60 unit packs and want the smallest matching batch size that uses whole packs of each: that is an LCM problem.
Simplify a ratio stored as two integers in A2 and B2:
=A2/GCD(A2, B2)
=B2/GCD(A2, B2)
Align recurring schedules — team A meets every 6 days, team B every 8, team C every 10; next joint meeting cadence:
=LCM(6, 8, 10)
Returns 120 days.
PRODUCT — multiply a range cleanly
PRODUCT multiplies all numbers in a list or range. It skips text the way SUM does, which makes it safer than a long A2*A3*A4*… chain.
=PRODUCT(B2:B6)
Compound factors — monthly multipliers in B2:B13 (for example 1.01 each month):
=PRODUCT(B2:B13)
gives the full-year growth factor. Combine with a starting value:
=A2*PRODUCT(B2:B13)
For a broader tour of everyday arithmetic alongside SUM / AVERAGE / MIN / MAX, see Top 4 Basic Arithmetic Functions Every Google Sheets User Should Know.
A few combined patterns
Percentage change, always positive display of error
=ABS(B2-A2)/A2
Format as percent. Use ROUND(..., 4) if you need a stable four-decimal rate for export.
Whole boxes needed from a unit count
=ROUNDUP(A2/B2, 0)
A2 = units ordered, B2 = units per box.
Normalize a value into 0–1 from a max scale
=A2/POWER(10, INT(LOG10(A2)))
Scientific-style mantissa when you also use logs; pair with the log guide linked above.
Safe unit price after discount, two decimal places
=ROUND(A2*(1-B2), 2)
A2 list price, B2 discount rate like 0.15.
Common mistakes
- Rounding too early. Intermediate
ROUNDcalls accumulate bias; round for presentation or for regulatory cutoffs only. - Wrong tool for negatives.
INTvsROUNDDOWNvs TRUNC disagree for negative inputs — test with-3.1before using on financial ledgers. - MOD with non-integers. Sheets accepts them, but for “every Nth row” logic stick to integers.
- GCD/LCM of non-integers or zeros. Stick to positive integers for predictable results.
- PRODUCT of an empty range. Returns
0, which can silently wipe a compound-factor chain — keep ranges tight.