formulastreakformulastreak.com · Formula cheat sheet
Formula cheat sheet
Every function in the course, with its syntax, what it does and an example. Works for Excel and Google Sheets (each lesson notes the differences).
Unit 2 · Ranges and functions
- SUM(range)
- Adds numbers, cells and ranges.
=SUM(D2:D9)Practise - AVERAGE(range)
- The average (arithmetic mean) of the numbers.
=AVERAGE(D2:D9)Practise - MAX(range)
- The largest number.
=MAX(G2:G13)Practise - MIN(range)
- The smallest number.
=MIN(F2:F13)Practise - COUNT(range)
- Counts the cells that contain numbers.
=COUNT(F2:F13)Practise - COUNTA(range)
- Counts the cells that aren't empty.
=COUNTA(D2:D13)Practise - ROUND(number, digits)
- Rounds a number to a number of digits.
=ROUND(D2/C2,2)Practise
Unit 3 · Decisions
- IF(condition, value_if_true, value_if_false)
- One value if a condition is TRUE, another if it's FALSE.
=IF(D2>C2,D2-C2,0)Practise - AND(condition1, condition2, …)
- TRUE if every condition is TRUE.
=AND(G2>=F2,E2>C2)Practise - OR(condition1, condition2, …)
- TRUE if any condition is TRUE.
=OR(H2="Pending",H2="Returned")Practise - IFS(test1, result1, test2, result2, …)
- The value for the first condition that's TRUE.
=IFS(G2>=40000,"Gold",G2>=35000,"Silver",TRUE,"Bronze")Practise - SWITCH(value, match1, result1, …, default)
- Compares a value with a list of cases and returns the match.
=SWITCH(D2,"Basic","B","Pro","P","Team","T","?")Practise - IFERROR(value, fallback)
- A fallback value if the formula gives an error.
=IFERROR(C2/B2,0)Practise - ISBLANK(cell)
- TRUE if the cell is empty.
=ISBLANK(E2)Practise
Unit 4 · Lookups
- VLOOKUP(key, table, column, FALSE)
- Looks down the first column and returns a value from that row.
=VLOOKUP(B2,$G$2:$J$8,4,FALSE)Practise - MATCH(key, range, 0)
- The position of a value in a row or column.
=MATCH(M1,H2:H8,0)Practise - INDEX(range, n)
- The value at a row and column position in a range.
=INDEX(I2:I8,M1)Practise - XLOOKUP(key, lookup_range, return_range)
- Finds a value in one range and returns the match from another.
=XLOOKUP(B2,$G$2:$G$8,$I$2:$I$8)Practise
Unit 5 · Conditional totals
- SUMIF(range, criteria, sum_range)
- Adds the cells that meet one condition.
=SUMIF(E2:E13,"Plants",I2:I13)Practise - SUMIFS(sum_range, range1, criteria1, range2, criteria2, …)
- Adds the cells that meet every condition.
=SUMIFS(F2:F13,E2:E13,"Plants",H2:H13,"Shipped")Practise - COUNTIF(range, criteria)
- Counts the cells that meet one condition.
=COUNTIF(B2:B9,"North")Practise - COUNTIFS(range1, criteria1, range2, criteria2, …)
- Counts the rows that meet every condition.
=COUNTIFS(B2:B9,"South",C2:C9,">10000")Practise - AVERAGEIFS(average_range, range1, criteria1, …)
- The average of the cells that meet every condition.
=AVERAGEIFS(C2:C9,B2:B9,"East")Practise - MAXIFS(max_range, range1, criteria1, …)
- The largest value among the cells that meet every condition.
=MAXIFS(E2:E9,B2:B9,"North")Practise
Unit 6 · Text and dates
- TRIM(text)
- Removes extra spaces from text.
=TRIM(A2)Practise - PROPER(text)
- Capitalises The First Letter Of Each Word.
=PROPER(A3)Practise - LEFT(text, n)
- Characters from the start of the text.
=LEFT(B2,4)Practise - RIGHT(text, n)
- Characters from the end of the text.
=RIGHT(C2,4)Practise - MID(text, start, n)
- Characters from the middle of the text.
=MID(C2,4,2)Practise - FIND(what, text)
- Where one text starts inside another (case-sensitive).
=FIND("@",B2)Practise - LEN(text)
- How many characters the text has.
=LEN(A2)Practise - TEXT(value, format)
- Formats a number or date as text, like "$#,##0.00".
=TEXT(G2,"$#,##0.00")Practise - TEXTJOIN(separator, ignore_empty, range)
- Joins text with a delimiter between the pieces.
=TEXTJOIN(" / ",TRUE,C2:C9)Practise - DATE(year, month, day)
- Builds a date from a year, month and day.
=DATE(B2,C2,D2)Practise - MONTH(date)
- The month (1 to 12) of a date.
=MONTH(D2)Practise - EOMONTH(date, months)
- The last day of the month, a number of months away.
=EOMONTH(F2,0)Practise
Unit 7 · Dates and time maths
- DATEDIF(start, end, unit)
- Whole years, months or days between two dates.
=DATEDIF(C2,$H$2,"Y")Practise - YEARFRAC(start, end, basis)
- The fraction of a year between two dates.
=YEARFRAC(D2,$H$2,1)Practise - EDATE(date, months)
- The date a number of months before or after a date.
=EDATE(C2,3)Practise - NETWORKDAYS(start, end, holidays)
- Working days between two dates (Monday to Friday).
=NETWORKDAYS(C2,D2,$H$2:$H$4)Practise - WEEKDAY(date, 2)
- The day of the week of a date, as a number.
=WEEKDAY(D2,2)Practise - WORKDAY(start, days, holidays)
- The date a number of working days away.
=WORKDAY(C2,5,$H$2:$H$4)Practise - ISOWEEKNUM(date)
- The ISO week number of a date.
=ISOWEEKNUM(D2)Practise - NETWORKDAYS.INTL(start, end, weekend, holidays)
- Working days between two dates, with your own weekend.
=NETWORKDAYS.INTL(C2,D2,11,$H$2:$H$4)Practise - MOD(number, divisor)
- The remainder after dividing.
=MOD(D2-C2,1)Practise
Unit 8 · Cleaning text at scale
- SUBSTITUTE(text, old, new)
- Replaces one piece of text with another.
=SUBSTITUTE(B2," ","")Practise - CLEAN(text)
- Removes non-printing characters from text.
=CLEAN(E5)Practise - CHAR(code)
- The character with this code number (10 is a line break).
=SUBSTITUTE(E2,CHAR(160)," ")Practise - TEXTAFTER(text, delimiter)
- The text after a delimiter.
=TEXTAFTER(B2," ")Practise - VALUE(text)
- Turns text that looks like a number into a number.
=VALUE(D2)Practise - SUMPRODUCT(range1, range2)
- Multiplies ranges item by item, then adds the results.
=SUMPRODUCT(F2:F13,G2:G13)Practise
Unit 9 · Data quality
Unit 11 · Dynamic arrays
- FILTER(array, include)
- Keeps the rows that meet a condition (spills).
=FILTER(A2:A9,B2:B9="North")Practise - ROWS(range)
- How many rows a range or array has.
=ROWS(A2:A9)Practise - UNIQUE(range)
- The distinct values of a range (spills).
=UNIQUE(B2:B17)Practise - SORT(array, column, order)
- Sorts a range (spills).
=SORT(C2:C9,1,-1)Practise - SORTBY(array, by_range, order)
- Sorts a range by another range (spills).
=SORTBY(A2:A9,C2:C9,-1)Practise - TAKE(array, n)
- Keeps rows or columns from the start or end of an array.
=TAKE(SORTBY(A2:A9,C2:C9,-1),3)Practise - SEQUENCE(rows)
- A list of numbers in rows and columns (spills).
=DATE(B1,SEQUENCE(12),1)Practise
Unit 12 · LET and LAMBDA
- LET(name, value, …, calculation)
- Names values inside a formula, then calculates with them.
=LET(total,F2*G2,tax,total*$L$1,total+tax)Practise - LAMBDA(parameter, …, calculation)(value, …)
- Makes your own function from parameters and a calculation.
=LAMBDA(q,p,q*p)(B2,C2)Practise - MAP(range1, range2, LAMBDA(a, b, calculation))
- Runs a LAMBDA on every value of an array.
=MAP(B2:B6,C2:C6,LAMBDA(q,p,q*p))Practise - BYROW(range, LAMBDA(row, calculation))
- Runs a LAMBDA on each row and returns one result per row.
=BYROW(C2:E9,LAMBDA(r,SUM(r)))Practise - SCAN(start, range, LAMBDA(total, value, calculation))
- Like REDUCE, but returns every step (spills).
=SCAN(0,B2:B7,LAMBDA(t,v,t+v))Practise
Unit 13 · Statistics
- MEDIAN(range)
- The middle number.
=MEDIAN(D2:D13)Practise - MODE.SNGL(range)
- The most common number.
=MODE.SNGL(C2:C13)Practise - STDEV.S(range)
- Standard deviation of a sample.
=STDEV.S(C2:C13)Practise - CORREL(range1, range2)
- The correlation coefficient of two ranges.
=CORREL(C2:C9,E2:E9)Practise - RANK.EQ(value, range)
- The rank of a number in a list (ties share a rank).
=RANK.EQ(C2,$C$2:$C$13)Practise - PERCENTILE.INC(range, k)
- The k-th percentile of a range (k from 0 to 1).
=PERCENTILE.INC(D2:D13,0.9)Practise - QUARTILE.INC(range, quart)
- A quartile of a range (0 to 4).
=QUARTILE.INC(D2:D13,1)Practise
Unit 14 · Money and finance
- FV(rate, periods, payment, present_value)
- The future value of an investment.
=FV(B3,B4,0,-B2)Practise - PMT(rate, periods, amount)
- The payment per period of a loan.
=PMT(B3/12,B4*12,B2)Practise - NPER(rate, payment, amount)
- The number of payment periods of a loan or investment.
=NPER(B3/12,-400,B2)Practise - IPMT(rate, period, periods, amount)
- The interest part of one loan payment.
=-IPMT($B$3/12,G2,$B$4*12,$B$2)Practise - PPMT(rate, period, periods, amount)
- The principal part of one loan payment.
=-PPMT(B3/12,1,B4*12,B2)Practise - NPV(rate, cash_flows)
- The net present value of future cash flows.
=NPV(10%,B12:B16)+B11Practise - IRR(cash_flows)
- The internal rate of return of cash flows.
=IRR(B11:B16)Practise
Unit 15 · Reporting patterns
Practise this in 5 minutes
A cheat sheet reminds you; practice makes it stick. Lesson 1 has you typing real formulas on a live sheet, with instant feedback.
Start the first lesson