Local Government Pay and Workforce: Facts and Figures

June 2010

Excel Functions Guide

We have produced this handy guide to help you get to grips with some of the most useful functions in Excel. Unless otherwise specified all the examples relate to the following example range:

Database Functions

The main database function is called VLOOKUP. This is probably the single most useful function in all of Excel.
VLOOKUP - searches for a value in the leftmost column of a range and returns the value in the specified column.
The basic structure of a vlookup is as follows:
which in English means:
1. What are you searching for?
2. Where is your data?
3. Which column or your data do you want the result to come from?
4. Do you want an exact match 0 (or false) or near match 1 (or true).
So in the example data to the right if you wanted to find the value for Suffolk you would enter the following formula:
=vlookup("Suffolk",A1:B9,2,0)
Which would find the value that corresponded to Suffolk - in this case 5.
HLOOKUP - like vlookup but searches the top row of a range and returns the value in the specified row.
=hlookup(lookup_value, range, column, match)
e.g. =hlookup("Bedford", A1:B9,5,0) equals "Norfolk" (the fifth row in the column beginning "Bedford").
IF - returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
=if(test, value_if_true, value_if_false)
e.g. =if(A2="Cambridge", "Yes", "No")equals "Yes".
INDEX - Returns a value or the reference to a value within a table or range.
=index(range, row_no, [col_no])
e.g. =index(A1:B9, 8, 1) equals “Bristol” (the 8th row down and the 1st column across).
MATCH - searches for a value in a range and returns the relative position of that item.
=match(match_value, range, [match type])
e.g. =match("Norfolk", A1:A9) equals 5 (as it is fifth in that range).

Text Functions

Excel has many useful functions for manipulating text strings.

UPPER - change cell contents to upper case
=upper(cell_ref)
e.g. Use =upper("Welcome to Myriad Research") to get "WELCOME TO MYRIAD RESEARCH"
LOWER - change cell contents to lower case
=lower(cell_ref)
e.g. Use =lower("Welcome to Myriad Research") to get "welcome to myriad research"
PROPER - change cell contents to Sentence Case
=proper(cell_ref)
e.g. Use =proper("Welcome to Myriad Research ") to get " Welcome To Myriad Research"
TRIM - removes trailing spaces from cell
=trim(cell_ref)
e.g. Use =trim(" Myriad ") to get "Myriad"
RIGHT - provides requested number of characters from the right of the cell
=right(cell_ref, no_of_chars)
e.g. Use =right("Myriad Research", 6) to get "search"
LEFT - provides requested number of characters from the left of the cell
=left(cell_ref, no_of_chars)
e.g. Use =left("Myriad Research", 6) to get "Myriad"
MID - provides requested number of characters from the middle of the cell
=mid(cell_ref, start_no, no_of_chars)
e.g. Use =mid("Myriad Research", 5,2) to get "ad"
LEN – counts the number of characters in a particular cell
=len(cell_ref)
e.g. Use =len("Myriad Research") to get 15.
SUBSTITUTE – substitutes all instances (or a specified instance) of a character or string within a cell
= substitute (cell_ref, old_text, new_text, [instance_no])
e.g. Use =substitute("Myriad Research", “Myriad”, ”Effective”) to get "Effective Research”
FIND – finds an instance of a specified character or string within a cell
= find (find_text, within_text, [start_no])
e.g. Use =find(“i”, "Myriad Research", 1) to get 4.

Mathematical Functions

The following functions provide a useful basis for manipulating numerical data.

SUM - gives the total value of data held in several cells
=sum(range)
e.g. =sum(B1:B9) equals 39.
SUMIF - sumsonly cells that meet a certain criteria
=sumif(range, criteria)
e.g. =sum(B1:B9,2) equals 6 (sums only values of 2).
COUNT - counts only cells with numbers in them
=count(range)
e.g. =count(B1:B9) equals9.
COUNTA - counts the cells containing any data (including text).
=counta(range)
e.g. =counta(A1:A9) equals8.
COUNTBLANK - counts only blank cells
=countblank(range)
e.g. =countblank(A1:A9) equals2.
COUNTIF - count only cells that meet a certain criteria
=countif(range, criteria)
e.g. =countif(B1:B9,2) equals 3 (counts only values of 2).
AVERAGE - provides the mean average for the selected range.
=average(range)
e.g. =average(B1:B9) equals 4.3.
MAX- provides the maximum value in the selected range.
=max(range)
e.g. =max(B1:B9) equals 9.
MIN- provides the minimum value in the selected range.
=min(range)
e.g. =min(B1:B9) equals 2.
PERCENTILE- provides a chosen percentile for the selected range.
=percentile(range, percentile_value)
e.g. =percentile(B1:B9, 0.5) equals 4.
Percentile value is always >0 and <1. Percentile can also be used to calculate deciles (multiples of 0.1), upper (0.75) and lower (0.25) quartiles and median (0.5).
ROUND- rounds a number to a specified number of digits.
=round(cell_ref, digits)
e.g. =round(3.14159,3) equals 3.142.

Date Functions

Excel has many useful functions to help you calculate dates, time periods and working days.

NOW - provides today's date and the current time.
=now()
e.g. Enter =now() to get 10/14/2012 9:21 PM (updates on calculation and opening).
TODAY - provides today's date.
=today()
e.g. Enter =today() to get 10/14/12 (updates on calculation and opening).
WORKDAY - provides a future date that is a set number of working days away from the specified date.
=workday(date, working_days)
e.g. =workday(14/10/2012,30) equals 23/11/2012 (so 23/11/2012 is 30 working days from 14/10/2012).