Timedora

2 Formulas and Functions

Learn how spreadsheet formulas use references, operators, and functions to calculate, test, transform, and retrieve data, and how to check results reliably.

structure and operators

A starts with = and can combine cell references, values, operators, and functions. For example, =B2*C2 multiplies the values in cells B2 and C2, while =SUM(B2:B10) uses a built-in function to total a range. Using references instead of entering fixed values in every makes calculations easier to update when data changes.

Common operators include + for addition, - for subtraction, * for multiplication, / for division, and ^ for powers. Parentheses clarify or change calculation order: =(B2+C2)*D2 adds B2 and C2 before multiplying the result by D2. Comparison operators such as =, >, <=, and <> produce TRUE or FALSE. The ampersand (&) joins text. Some spreadsheet settings use semicolons instead of commas between function arguments.

Cell references when copying formulas

A cell reference can adjust or stay fixed when its is copied. Choose the reference type according to which parts should change:

  • Relative reference: B2 adjusts to the ’s new position when copied.

  • : $B$2 keeps both the column and row fixed.

  • Mixed reference: $B2 fixes the column, while B$2 fixes the row.

For example, a tax rate is in F1, =B2*$F$1 can be copied down a column. The sales reference changes by row, while the tax-rate reference remains fixed.

Calculation and criteria functions

Calculation functions can summarize a range or return results that meet criteria. In the examples below, product names are in A2:A100, quantities in B2:B100, prices in C2:C100, and regions in D2:D100.

  • =SUM(B2:B100) totals the quantities.

  • =AVERAGE(C2:C100) calculates the mean price.

  • =MIN(C2:C100) returns the lowest price; =MAX(C2:C100) returns the highest.

  • =COUNT(C2:C100) counts numeric entries; =COUNTA(A2:A100) counts populated cells.

  • =SUMIF(D2:D100,"West",C2:C100) adds prices for rows whose region is West.

  • =(C2:C100,D2:D100,"West",A2:A100,"Tea") adds prices where the region is West and the product is Tea. The sum range comes first, followed by pairs of criteria ranges and criteria; each criteria range should align in size with the sum range.

  • =COUNTIFS(D2:D100,"West",A2:A100,"Tea") counts rows matching both the West region and Tea product conditions.

Logic and conditional formulas

The function returns one result when a condition is true and another when it is false. For example, =(B2>=100,"Target met","Below target") returns “Target met” when the quantity is at least 100 and “Below target” otherwise.

Use AND when all combined conditions must be true, or OR when at least one must be true. In =(AND(B2>=100,D2="West"),"Review",""), “Review” appears only when the quantity is at least 100 and the region is West; otherwise, the result is an empty string. functions can be nested for more complex rules, but several separate tests can be harder to read and maintain.

Working with text

Text functions can combine, standardize, extract, or format text. For example, =A2&" - "&D2 joins a product name and region with a separator, =TRIM(A2) removes extra spaces around words, and =LEFT(A2,3) returns the first three characters. =TEXT(C2,"$0.00") displays a number in a specified text format. Because TEXT converts a value to text, use the original numeric value for later arithmetic when possible.

Dates and times

Spreadsheets store dates as values that can be formatted for display and used in calculations. =DATE(2026,10,12) constructs October 12, 2026, from its year, month, and day parts. YEAR, MONTH, and DAY extract parts of a date. Subtracting one valid date from another gives the number of days between them; apply a date format the result displays as a number.

Looking up matching information

A lookup searches for a key, such as a product code, and returns related information from another range. =(F2,A2:A100,C2:C100,"Not found",0) searches for the value in F2 within A2:A100 and returns the corresponding value from C2:C100. there is no match, it returns “Not found”; the match mode 0 specifies an exact match. is available in current Excel and Google Sheets, but may not exist in older spreadsheet versions.

A reliable workflow

Use a consistent workflow to create and check formulas:

  1. Check that each column contains the intended kind of data, such as real dates rather than date-like text.

  2. Write a with clear cell or range references, then test it on a known example.

  3. Copy or fill the down and check whether relative and absolute references behave as intended.

  4. Verify results by recalculating a small sample independently and checking that ranges include all relevant rows.

  5. a result is unexpected, inspect parentheses, function argument order, spelling, criteria quotation marks, and cell formats. Look for errors such as #DIV/0! for division by zero or #N/A for no lookup match.