Excel Formula Masterclass

This course is dedicated to exploring a number of formulas/functions available on Microsoft Excel.

The actual formulas/functions will be agreed in advance of the training with approximately three of the following to be covered;

1. Logical Functions

IF

o Simple

o Nested

o Compound:

• AND, OR, NOT, IFS

2. Lookup Functions

· VLOOKUP, HLOOKUP, XLOOKUP

· CHOOSE

· SWITCH

· MATCH

· INDEX

· UNIQUE

3. Statistical Functions

· COUNT, COUNTA, COUNTBLANK

· COUNTIF, SUMIF, AVERAGEIF

· COUNTIFS, SUMIFS, AVERAGEIFS

· MAXIFS, MINIFS

· MEDIAN, MODE

· RANK.EQ, RANK.AVG

· LARGE, SMALL

4. Math Functions

· SUBTOTAL

· ROUND, ROUNDUP, ROUNDDOWN

· MROUND, CEILING, FLOOR

· INT, TRUNC

· ODD, EVEN

· MOD

· RAND, RANDARRAY, RANDBETWEEN

· CONVERT

· AGGRAGATE

5. Date and Time Functions

· TODAY, NOW

· WEEKDAY

· NETWORKDAYS, WORKDAY

· DATEDIF

· EDATE, EOMONTH

6. Reference Functions

· OFFSET

· INDIRECT

7. Text Functions

· FIND, SEARCH, MID

· LEFT, RIGHT

· TRIM

· CONCATENATE, & SYMBOL, CONCAT, TEXTJOIN

· LOWER, UPPER, PROPER

· REPLACE, SUBSTITUTE

· TEXT, REPT, VALUE, LEN