Skip to content

Formula Foundations & Functions

Absolute Formula & Function Architecture

Syntax Fundamentals

All formulas must activate using an = prefix. Double-click or select a cell and press F2 to modify references safely.

The Foundation Commands

  • =SUM(range) — Adds total values.
  • =AVERAGE(range) — Evaluates statistical mean.
  • =COUNT(range) — Tallies cells containing numbers only.
  • =COUNTA(range) — Tallies all non-empty cells (text and numbers).
  • =MAX(range) — Extracts peak value.
  • =MIN(range) — Extracts baseline value.
  • =PRODUCT(range) — Multiplies arrays together.

Cell Referencing Mechanics

  • Relative Referencing: Standard reference (e.g., A1) that shifts naturally when dragged across rows or columns.
  • Absolute (Fixed) Referencing: Locks reference points completely using dollar signs ($A$1). Use shortcut Fn + F4 to add tracking tokens automatically.
  • Named Ranges: Highlight a static range and assign it a permanent name via the Name Box or Define Name. Use this to keep formulas readable when referencing non-evolving structural datasets.

Advanced Logical, Condition, & Lookup Functions

Logical Decision Operators

  • =IF(logical_test, value_if_true, value_if_false)
    • Example: =IF(B9="bad","would not go", "will go")
  • =AND(condition1, condition2) — Evaluates to TRUE if all arguments match.
  • =OR(condition1, condition2) — Evaluates to TRUE if at least one argument matches.

The IF / IFS Conditional Families

Summation & Aggregation

  • =SUMIF(range, criteria, [sum_range]) — Sums a range based on a single condition.
  • =SUMIFS(sum_range, criteria_range1, criteria1, ...) — Evaluates multiple layout criteria simultaneously.
    • Wildcard Utility: Use asterisks (*) to look up partial matches. For example, *apple* matches any string containing "apple" anywhere inside the cell.

Averages & Counts

  • =AVERAGEIF(range, criteria, [average_range]) — Averages values matching a condition.
  • =AVERAGEIFS(average_range, criteria_range1, criteria1, ...) — Multiple criteria mean validation.
  • =COUNTIF(range, criteria) — Tallies matching instances.
  • =COUNTIFS(criteria_range1, criteria1, ...) — Tallies multi-layered conditional variables.

Extreme Values Under Criteria

  • =MAXIFS(max_range, criteria_range1, criteria1, ...) — Extracts highest value among specific criteria matching.
  • =MINIFS(min_range, criteria_range1, criteria1, ...) — Extracts lowest value among specific criteria matching.

String Extraction, Manipulation, & Dates

Text Parsing Operations

  • =RIGHT(text, [num_chars]) — Extracts text starting from the far right.
  • =LEFT(text, [num_chars]) — Extracts text starting from the far left.
  • =MID(text, start_num, num_chars) — Extracts a string from the middle of a cell based on a starting position.
  • =UPPER(text) / =LOWER(text) / =PROPER(text) — Systematically normalizes character capitalization.
  • =CONCAT(text1, ...) — Merges fields together without spaces.
  • =TEXTJOIN(delimiter, ignore_empty, text1, ...) — Combines strings using a custom separator (e.g., commas) while skipping empty cells.

Advanced Text Transformation

  • =SUBSTITUTE(text, old_text, new_text) — Swaps specific characters. Case-sensitive. (e.g., =SUBSTITUTE(D5,"S","XS")).
  • =REPLACE(old_text, start_num, num_chars, new_text) — Overwrites a specific section of text based on its position. (e.g., =REPLACE(E20,3,1,"A")).
  • =SWITCH(expression, value1, result1, ...) — Evaluates a value against a list of pairs and returns the first matching result.
  • =TEXT(value, format_text) — Extracts and formats raw values into specific text arrangements:
    • =TEXT(D5,"#") — Rounds decimal values to the nearest whole integer block.
    • =TEXT(F23,"mmmm") — Extracts full chronological month names (e.g., "July").
    • =TEXT(F38,"hh:mm am/pm") — Converts timestamps into standard 12-hour formatting.
    • =TEXT(F74,"dddd") — Extracts full weekday names (e.g., "Tuesday").

Date & Time Calculations

  • =TODAY() — Displays the current date dynamically.
  • =NOW() — Displays the exact current date and live timestamp.
  • =DAY(date) / =MONTH(date) / =YEAR(date) — Extracts individual date components.
  • =HOUR(time) / =MINUTE(time) / =SECOND(time) — Extracts individual time units.
  • =DAYS(end_date, start_date) — Calculates the raw number of days between two dates.
  • =NETWORKDAYS(start_date, end_date, [holidays]) — Calculates the number of working days between two dates, excluding weekends (Saturday and Sunday) and optional custom holidays.
  • =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) — Calculates working days with custom weekend setups (e.g., choosing Friday/Saturday as weekends instead).