Excel for finance and audit. Functions explained with worked examples, in English and Dutch.
The examples come from audit work: XAF auditfile extracts, trial balances, aging lists, cut-off testing and journal entries.
All functions (99 functions)CombinationsKeyboard shortcutsLearning paths
Most used functions
All functions- MathSUMSOMAdds up numbers, cells and ranges, skipping anything in a range that is not a real number.
=SUM(C2:C6)=SUM(C2:C6) - LogicalIFALSChecks a condition and returns one value when it holds and another value when it does not.
=IF(B2>=1500, "Investigate", "Below threshold")=IF(B2>=1500; "Investigate"; "Below threshold") - Lookup & referenceXLOOKUPX.ZOEKENFind a value in one list and bring back the matching item from another.
=XLOOKUP(E2, $A$2:$A$6, $C$2:$C$6)=XLOOKUP(E2; $A$2:$A$6; $C$2:$C$6) - MathSUMIFSSOMMEN.ALSAdds the numbers in one range for the rows where every condition you set on other ranges is met.
=SUMIFS(C2:C7, B2:B7, F1)=SUMIFS(C2:C7; B2:B7; F1) - LogicalIFERRORALS.FOUTReturns the result of a calculation, or a replacement value of your choice when that calculation ends in any error.
=IFERROR(C2 / B2, "No base")=IFERROR(C2 / B2; "No base") - TextLEFTLINKSReturns a chosen number of characters from the start of a text, counting from the first character.
=LEFT(A2, 3)=LEFT(A2; 3) - TextRIGHTRECHTSReturns a chosen number of characters from the end of a text, counting back from the last character.
=RIGHT(A2, 4)=RIGHT(A2; 4) - TextMIDDEELReturns a piece from inside a text: you give the position where it starts and how many characters it runs.
=MID(A2, 5, 4)=MID(A2; 5; 4) - TextTRIMSPATIES.WISSENRemoves spaces at the start and end of a text and collapses repeated spaces between words to one.
=TRIM(B2)=TRIM(B2)
Browse by category
What's new
Regular expressions: REGEXTEST, REGEXEXTRACT and REGEXREPLACE, an explainer, a regex lookup combination, and match mode 3 on XLOOKUP and XMATCH. Combination pages now have the side panel too.
Try it yourself: on the lookup and criteria pages you can now change the mode or type a criterion and watch the sheet respond.
8 function pages added: