Spreadsheet Formula and Function

A formula is an instruction you write in a cell, and it begins with = — the sign that tells the application to calculate rather than to store what you typed as a label. A function is a named operation the application already provides, such as SUM or AVERAGE, used inside a formula. So =B2+C2 is a formula without a function, =SUM(B2:C2) is a formula built from one, and the two words are not interchangeable even though they are often swapped.

Formulas combine values, cell references, and operators — +, -, *, / — and parentheses decide what happens first, exactly as in arithmetic. =(120+150+90)/3 averages three numbers, while the same expression without parentheses divides only the last one. Because a formula recalculates whenever a referenced cell changes, it is a standing rule rather than a one-time answer, which is the whole reason to prefer it to a typed-in total.

Functions take arguments in parentheses, usually a range, and most of everyday work needs only a handful: SUM, AVERAGE, MIN, MAX, COUNT and COUNTA. Knowing what each ignores matters more than knowing many of them — SUM skips text inside a range, COUNT takes only numbers while COUNTA takes anything non-empty, and AVERAGE divides by the count of numeric values rather than by the number of rows.

Prefer the range form. =SUM(B2:B4) keeps working when a row is inserted in the middle and does not collapse into #REF! when one is deleted, whereas =B2+B3+B4 ignores the inserted row and turns into #REF! after a deletion. When a calculation has no dedicated function, a formula still does it, and when a formula grows long enough that nobody can read it, that is a signal to compute it in stages across helper columns rather than to admire it.

References:

• Overview of formulas in Excel
• SUM function