This can be used to return total values in a range, if the range is filtered only the selected items will be included:

• Syntax: =SUBTOTAL(Function Number,range)
• Example =SUBTOTAL(2,C:C)
• Commonly used function numbers are:
  • 1 - AVERAGE
  • 2 - COUNT (Counts the number of cells)
  • 3 - COUNTA (Counts the number of non-empty cells)
  • 4 - MAX
  • 5 - MIN
  • 6 - SUM

To ignore hidden values, prefix the operator with 10 i.e. average becomes 101.

Counts the number of cells within a range that meet the given criteria:

• Syntax: =COUNTIF(range,criteria)

• Example: =COUNTIF(D:D,"Woking")

  In this example column D contains the employee’s location, the COUNTIF function returns the number of employees in Woking.

To create a result based upon whether a condition is true or false:

• Syntax: =IF(condition to meet, return value if true, return value if false)
• Example: =IF(@Age<17,"Too young to drive","")

If required, either of the return values can be left blank, as in the above example - if the employee was under 17 the text "Too young to drive" would appear, if they were 17 or older the field would remain blank.

Used in Ciphr to display Descriptions and other values from unlinked tables:

• Syntax: =VLOOKUP(Cell reference,table,Return Column number,Exact match)
• Example: =VLOOKUP([@[LocationID]],CIPHR_LOCATIONS,3,true)

This example looks in the CIPHR_LOCATIONS table for a value matching the LocationID value and returns the value in column 3 (location description) if it matches exactly.

Used in Ciphr to calculate the year from a date value:

• Syntax: =YEAR(Date value)
• Example: =YEAR([@[Start Date]])

This example calculates the year in which an employee started.

The TEXT function lets you change the way dates and numbers appear by applying formatting to it with format codes:

• Syntax: =TEXT(Date value,Format)

In its simplest form, the TEXT function says:

• =TEXT(Value you want to format, "Format code you want to apply")

If the Start date of the record is 03/02/2020 the following results can be obtained:

• =TEXT([@[Start Date]],"DDDD") returns “Monday”
• =TEXT([@[Start Date]],"DDD") returns “Mon”
• =TEXT([@[Start Date]],"DD") returns “03”
• =TEXT([@[Start Date]],"D") returns “3”
• =TEXT([@[Start Date]],"MMMM") returns “February”
• =TEXT([@[Start Date]],"MMM") returns “Feb”
• =TEXT([@[Start Date]],"MM") returns “02”
• =TEXT([@[Start Date]],"M") returns “2”
• =TEXT([@[Start Date]],"DD-MM-YY") returns “03-02-20”

For number use, if an employee’s basic pay is 23456.78:

• Applying =TEXT(Basic_Pay,"£0") returns “£23456”