← Back to the syllabus
Reference
Every function this site knows
59 functions, each with what it is for, what comes back, every argument named, and worked examples on a real ledger extract. The results are not typed in. They are worked out by the same evaluator that marks your answers, so an example here cannot claim something the formula would not do.
Text
13 functions
TRIMClean the spaces.Reference onlyLENCount the characters.Reference onlyMIDGrab part of the text.Unit 10FINDWhere is it?Unit 10SUBSTITUTESwap this text for that text.Reference onlyREPLACEChange characters at this position.Reference onlyLEFTTake characters from the start.Unit 10RIGHTTake characters from the end.Unit 10SEARCHWhere is it, ignoring capitals?Reference onlyCONCATENATEJoin text together.Unit 03UPPERMake it all capitals.Reference onlyLOWERMake it all lower case.Reference onlyPROPERGive every word a capital letter.Unit 04
Logic
6 functions
Checking
4 functions
Numbers
5 functions
Dates
9 functions
TODAYToday's date, which changes every day.Unit 27DATEBuild a date from its three parts.Unit 25YEARWhich year is this date in?Unit 25MONTHWhich month is this date in?Unit 25DAYWhich day of the month is this?Unit 25EOMONTHThe last day of a month.Unit 26EDATEThe same day, a number of months away.Unit 26DATEDIFHow long between two dates.Unit 27DAYSHow many days between two dates.Unit 24
Totals and counts
16 functions
SUMAdd up the numbers.Unit 01COUNTCount the cells holding numbers.Reference onlyCOUNTACount the cells that are not empty.Reference onlyAVERAGEAverage the numbers.Unit 02MINThe smallest number.Unit 13MAXThe largest number.Unit 13COUNTIFCount the rows that match one condition.Unit 07COUNTIFSCount the rows where every condition holds.Unit 15SUMIFTotal the matching rows.Unit 08SUMIFSTotal the rows where every condition holds.Unit 14AVERAGEIFAverage the matching rows.Reference onlyAVERAGEIFSAverage the rows where every condition holds.Reference onlyMAXIFSThe largest number among the matching rows.Reference onlyMINIFSThe smallest number among the matching rows.Reference onlySUMPRODUCTMultiply two columns row by row, then add the answers.Unit 11SUBTOTALTotal what the filter left showing.Unit 16
Lookup
5 functions
Ranges
1 functions