 Library Subject Guides

# T2: Creative functions

Spreadsheets were developed as a tool to store, analyse and manipulate numerical data. They are now commonly used for working with sets of data containing both text and numbers, and for generating graphs and charts. They're also brilliant. Let's play with

Here we take a look at some of the things that appear in the dropdown prompt when you start typing into a formula, starting with an exploration of named ranges before getting properly stuck in with functions: in particular, conditional functions, but we take a peek at some helpful date and time functions, and some other useful functions too.

## Named ranges

Most formulae need to reference cells or ranges. An alternative to the usual reference style (e.g. A1) is to assign names which are used in place of the cell or range address.

For example, we might give cell A1 the name 'Cost' and cell B1 the name 'Quantity'. Then, rather than writing =A1*B1 we could enter =Cost*Quantity.

The potential benefits of this approach are:

• making spreadsheet formulae more meaningful
• avoiding the need for all those dollars in absolute references

You can name: a single cell, a whole column or row, or a rectangular range of cells.

To avoid any ambiguity in formulae, there are some naming rules:

• They must not contain spaces, dashes or ‘quotes’, but underscores (_) are allowed
• They must not be 'reserved words' (like TRUE or FALSE), or be a valid cell reference (e.g. AB123)

### Creating named ranges

1. Select the cell or range to be named.
2. After selecting the range, enter its name into the Name Box to the left of the formula bar.
3. After typing in the name you must press Enter to confirm it. If you are working with listed values and the number of rows in use may change, select entire columns rather than just the rows currently in use.  You can also create named ranges from Formulas > Defined Names  You can also create named ranges from the Named ranges side-panel at Data > Named ranges (or by right-clicking the selection and choosing Define the named range...). Enter a name for your range, check the range is correct, and choose Done.

### Using named ranges

Once created, the names can be used anywhere in a formula or function where a cell reference or range would normally be used.

Here's an example using named columns and a named cell:

A B C D E F fx VAT rate 20% Item Price Quantity Cost Item VAT Inc Cost A4 paper (5 reams) 23.99 2 47.98 9.60 £57.58 Pens (box of 10) 5.50 5 27.50 5.50 £33.00 Pencils (box of 20) 3.90 10 39.00 7.80 £46.80 Business cards (1,000) 22.50 2 45.00 9.00 £54.00 Board markers (pack of 8) 4.75 3 14.25 2.85 £17.10

The following named ranges have been created (click on them to highlight them on the spreadsheet):

VATRate
(B1)
Price
(B:B)
Quantity
(C:C)
Cost
(D:D)
ItemVAT
(E:E)

In cell E4 we need to calculate the value added tax (VAT) payable for two boxes of A4 paper. Using cell references our formula would be:

=D4*\$B\$1

But we've given cell B1 the name VATRate. Because this named range refers to one specific cell, it will effectively work as an absolute reference, so now we can use this named range in our formula instead:

=D4*VATRate Be aware that names for named ranges are case sensitive: we could have a separate cell called VATrate, and another called vatRate, etc... although that way confusion lies!

We can go even further than this, though. Because we've named column D Cost, we can use that in our formula too. The spreadsheet is clever enough to work out, from the context of the cell, that any reference to Cost in cell E4 needs to refer to the corresponding item in the range Cost: namely the item from row 4. The latest versions of Excel require an extra nudge to tell it to do this, in the form of an 'at' sign (@) before the range name, because the latest versions of Excel can do special things with named ranges that are whole columns (if you get a #SPILL! error in Excel, it's probably because you're missing an "@"; if you're using "@"s but get a "That function isn't valid" error, it's because you're on a pre-@ version of Excel so don't need the "@"s).

The formulae required, then, are: =@Cost*VATRate (Excel for Microsoft 365)

=Cost*VATRate (Excel 2019 and earlier) =Cost*VATRate

Use the buttons below to display the underlying calculations required for Excel and Google Sheets: Excel for
Microsoft 365  Excel (older versions)

A B C D E F fx VAT rate 20% =@Price*@Quantity =@Cost*VATRate =@Cost+@ItemVAT Item Price Quantity Cost Item VAT Inc Cost A4 paper (5 reams) 23.99 2 47.98 9.60 £57.58 Pens (box of 10) 5.50 5 27.50 5.50 £33.00 Pencils (box of 20) 3.90 10 39.00 7.80 £46.80 Business cards (1,000) 22.50 2 45.00 9.00 £54.00 Board markers (pack of 8) 4.75 3 14.25 2.85 £17.10

#### Entering names in a formula

When writing a formula, simply type in the name where you would normally enter the cell or range reference. When you start typing the name, any matching named ranges will appear among the list of functions — you can double-click on a named range in this list to add it to your formula without having to type the whole thing. In Excel you can also enter a named range by:

• Selecting the name from the Name Box dropdown
• (if it refers to a single cell) clicking on the cell in question (the name will be entered, not the cell reference)
• (if it refers to a column / row) clicking on the column letter / row number In Google Sheets, clicking on a cell will always give its reference rather than its name. Even clicking on the ranges in the "Named ranges" side-menu will use the underlying reference in the formula.

### Editing and deleting named ranges

To list all named cells/ranges in the current spreadsheet and to edit/delete them:  1. Select Formulas > Defined Name > Name Manager

2. Choose the range and hit Edit or Delete as appropriate.  1. Go to Data > Named ranges to open the 'Named ranges' side-panel

2. Locate the named range, hover over it, and hit the Edit pencil

3. You can then edit the range, or Delete it with the bin icon.

## Finding and inserting functions

So far, when we've been entering functions, we've been doing so via the "Σ" menu of common functions. But, now it's time for us to explore some more advanced functions.

There's an extensive library of functions, arranged by type, at: Formulas > Function Library Insert > Function

Functions are also listed as you type into a formula. Hovering over a function name will give you a short tooltip summary of what it does.

There's also these summaries of all the available functions in each program (with links to help on how to use them):

### Excel's "Insert Function" and "Function Arguments" dialogue boxes

Excel has an additional way to enter functions: the Insert Function dialogue. It's another way to see all the functions by category, to search the functions list, and to get further help. To use the "Insert Function" dialogue:

1. Select the cell in which you wish to enter the function (but don’t start typing anything);
2. Select the Insert Function control (the fx to the left of the formula bar) - this will launch the dialogue;
3. Locate the function you want to use by either, searching, choosing a category, or selecting from the "Most Recently Used" list (if you’ve used it recently);
4. Once a function has been selected, you can find out more about it by following the "Help on this function" link;
5. To use the function, choose OK – this opens the Function Arguments dialogue. The Function Arguments dialogue can be useful when you're using a function for the first time. Each bit of information required by a function (its 'arguments') is plotted out in a series of boxes, with contextual help and a summary of how each step evaluates.

.

### Google Sheets' "arguments help" pop-up panel

The arguments help pop-up appears in Google Sheets when you open the brackets of a function to start adding its 'arguments': The amount of help shown can be expanded and contracted according to need, using the shrink/expand chevron toggle. Sometimes the panel can get in the way, in which case you can close it with the cross icon. If closed, the panel can be reactivated by clicking the question mark icon (?) to the left of the cell / formula bar (or by pressing F1).

## Conditional functions

Conditional functions check whether a value (usually a value in a cell) meets a logical test where the outcome can be either true or false. They then generate a result based on that true/false outcome.

Imagine I toss a coin. The result can be heads or tails. I could apply the test "The coin is showing heads: true or false?". If my toss gives heads then it meets that test: it's true; if my toss gives tails then it fails the test: it's false.

I could then do something off the back of this. I might say that if "The coin is showing heads" is true I should get up and make a cup of tea, whereas if it's false I should get up and make a cup of coffee instead.

We can do a similar sort of thing in spreadsheets. Take this example:

TypeSymbolExampleLogical resultWorks with...
Greater than>C2>D2TrueNumbers
C2>C4False
Less than< D2<D4True Numbers
C2<D4False
Greater than or equal to>= C2>=D4True Numbers
D2>=D4False
Less than or equal to<= D2<=D4True Numbers
C2<=D2False
Equal to= A2=C1True Text
A2=D1False
C2=D4True Numbers
D2=D4False
Not equal to<> A6<>C1True Text
A6<>D1False
D2<>D4True Numbers
C2<>D4False As well as referencing cells you can also directly enter values (numbers or “text”) into the arguments of a conditional function, but as always, it is better practice to reference a cell containing the value — otherwise you have to edit the function to change the tested value.

### IF()

The IF function allows you to compare values and give a different response depending on the outcome. It needs three parameters (or 'arguments'):

=IF( An expression that is true or false, Result if true, Result if false )

The 'Result' portions of the above can be:

• a value (number or "text") that you wish to be placed in the spreadsheet as the result of the function
• a reference to a cell containing a value
• further formula to perform a calculation.

Let's look at an example:

A B C D E fx =IF(D4

To indicate whether each student had passed, cells in column E contain an IF function to test if the mark (in column D) has reached the pass mark (cell B1, which we've given the name passMark). There are a few options we could go with in cell E4. Here's one

:

=IF(D4<passMark,"Fail","Pass")

...and here's another:

=IF(D4>=passMark,"Pass","Fail")

This example uses text as its output, but we could equally incorporate calculations, for instance applying a discount. Here's an example taking that approach:

#### Nesting IFs

Suppose there are 3 possible outcomes to an exam:

ScoreMark
<60Fail
60-80Pass
>80Distinction

Since one IF function only has two outcomes, it cannot give all three possibilities. However, combining two IF functions, one inside another, lets you test for all three results.

The first test can be done with the following formula:

=IF(A1<60,"Fail","Pass or distinction")

This will identify marks that are a fail, but if the result is false, we now need to test between a pass and a distinction. To do this we insert a second IF:

=IF(A1<60,"Fail",IF(A1>60,"Distinction","Pass")) Note that the second IF is not preceded by an equals sign (=) — that's only needed at the very start of your formula.

While this approach of nesting multiple IF functions could be extended further, it gets messy and unwieldy after a few possible outcomes. Using range lookups is simpler in these cases. We'll take a look at those later...

Nesting can be applied with any functions and is a very powerful technique; however the formulae involved can easily become confusing. Always test your formula carefully to make sure you get the expected results.

### Getting conditional with the basic functions (SUM, COUNT, etc.)

All of the basic functions we looked at earlier (the ones on the Σ "Functions" menu) have variants that incorporate conditional tests. With these, cell values can be summed, averaged, counted, etc., only if they meet certain defined criteria.

The conditions don't have to apply merely to the cells being summed, averaged, counted, etc. — the tests can be applied to associated data in a table, e.g. data on the same row (or on the same column if working horizontally rather than vertically).

These functions typically require the following arguments:

• A range of cells to test against a criterion
• A value or expression (the criterion) to test against
• Optionally, a range of corresponding cells to SUM, AVERAGE, etc. (if you want to SUM / AVERAGE a different range to that being tested)

There are also multiple-criteria versions of these functions, that let you apply tests to more than one column in a table of data. Here's a list of all the variations:

FunctionArguments
SUMIFRange to test, criterion, [optional corresponding range to sum]
SUMIFSRange to sum, range to test, criterion, [2nd range to test, 2nd criterion... etc.]
AVERAGEIFRange to test, criterion, [optional corresponding range to average]
AVERAGEIFSRange to average, range to test, criterion, [2nd range to test, 2nd criterion... etc.]
COUNTIFRange to test/count, criterion
COUNTIFS1st range to test/count, criterion, [2nd range to test/count, criterion... etc.]
MAXIFSRange in which to find the highest value, range to test, criterion, [2nd range to test, 2nd criterion... etc.]
MINIFSRange in which to find the lowest value, range to test, criterion, [2nd range to test, 2nd criterion... etc.]

Perhaps we ought to consider some example data:

A B C D E F fx Student ID Year Major Exam Mark Awards Output: 001 1 Cake 81 1 002 1 Chocolate 76 2 003 1 Pies 56 1 004 1 Cake 58 1 005 1 Chocolate 92 0 006 2 Pies 74 1 007 2 Pies 72 1 008 2 Pies 61 2 009 2 Chocolate 81 1

Tested ranges
Matches in tested ranges
Evaluated cells

Select the functions below to see an example in action (check the formula bar for the formula being used):

SUMIF
SUMIFS
AVERAGEIF
AVERAGEIFS
COUNTIF
COUNTIFS
MAXIFS
MINIFS While the above example uses a standardised tabular layout, the basic versions of these functions can work with ranges of any shape or size and needn't be limited to one dimension. However, when more than one range is being used (as with the -IFS functions), all ranges used must be of the same size.

#### Criteria

While the criteria in the above example were all 'hard-coded' into the formula, they can, as with most formulae, be provided from cell references. But there are certain foibles to be aware of, and tricks it's useful to know about:

ConditionHard-code exampleCell reference example
For an exact text match, enter the text in quotes (note that the matching is not case sensitive)."Pies"A1
To match only part of a string of text (e.g. the word "pie" in the string "Apple pie and custard") you can 'stitch' wildcard characters to the condition (the "*" stands in for any text (or none), and the "&" 'glues' bits of a string together)."*"&"Pie"&"*""*"&A1&"*"
For an exact number match, simply enter the number.2A1
Logical conditions can be applied to a stand-alone number but need to be treated like text strings (if the condition is written into the referenced cell, (e.g. as >=70) you can just reference the cell).">=70"">="&A1

## Calculations and functions for dates and times

We saw earlier how dates are stored as whole numbers and times are stored as decimal fractions. This means that we can perform calculations using dates and times. When performing calculations with dates and times, be aware that you'll have to format the results appropriately. The spreadsheet won't necessarily format your answer as a date/time by default.

### Basic calculation with dates

Since dates are represented as whole numbers, adding or subtracting a whole number to a date will give a different date:

A B C D fx Start date Days End date Duck conference 01/01/11 8 =B2+C2 Goose conference =D3-C3 7 21/01/11

#### Difference between two dates

Since dates are represented as whole numbers, subtracting one date from another will give the number of days between those dates:

A B C D fx Start date End date Duration Coot conference 01/01/11 05/01/11 =C2-B2 Moorhen conference 14/01/11 20/01/11 =C3-B3

### Time and duration

By default, times are treated as a time of day, so 12:00 means noon. But time can also be used as a duration, and can be used to calculate new times, even on a new day:

A B C D E fx Start time Duration End time Swan conference 12:00 2:00:00 14:00 ← =B2+C2 Fish conference 21:00 5:30:00 02:30 ← =B3+C3

In many cases, there's no need for us to distinguish between time and duration. However, unexpected results may start happening if we try to total durations exceeding 24 hours!

In such cases, the cell(s) involved must be formatted as duration: 1. Select the cells you need to format
2. Right-click and select Format cells..., or go to Home > Number > Number format (dialogue toggle)
3. Select "Custom" from the Category list
4. Locate and select the format [h]:mm:ss and hit OK

The [ ]s around the "h" are what makes the spreadsheet know it's dealing with a duration rather than a time. 1. Select the cells you need to format
2. Go to Format > Number > Duration

Alternatively, you could set a custom format (Format > Number > Custom number format).

### Date and time functions

Let's take a look at some specialist functions for working with dates and times:

#### TODAY() and NOW()

Spreadsheets include some functions that are able to refer to the computer’s internal calendar and clock. These functions are unusual in that they require no arguments between their brackets:

=TODAY() Returns the current date (changes when the spreadsheet is opened on a new day) Returns both the current date and time as a single value (the time updates whenever a change is made to the spreadsheet) Be aware that since these values change with the day and time, they can't be used as a date/timestamp.

These functions can be used in calculations, for example to find out how many days there are until a deadline is due:

A B C fx Project Deadline Days to deadline Cake bake 31/05/21 =B3-TODAY()

#### Constructing and deconstructing dates and times

Here are a few functions for building dates and times or getting components out of them:

=DATE(year,month,day) Creates a spreadsheet date value from a year, a month (as a number from 1-12) and a day (as a number from 1-31) — useful for piecing together a date from its constituent parts. Creates a time value from an hour (i.e. 0-23), minute (i.e. 0-59), and second (i.e. 0-59) — it can deal with numbers not in those ranges too! These functions pull from a date value the integer values for the year, month (1-12), and day (1-31) respectively — effectively they're doing the opposite of DATE. Likewise, these pull out the component parts of a time value as integers (0-23; 0-59). This one works out the day of the week from a date value and returns a number from 1-7. The "type" argument lets you define what day is considered to be day 1. Returns the week number in the year for the given date. WEEKNUM ticks onto a new number every Sunday, ISOWEEKNUM every Monday Creates a spreadsheet date value from a text string that the spreadsheet could read as a date. Creates a spreadsheet time value from a text string that the spreadsheet could read as a time.

#### Date and time calculation functions

While it's relatively easy to do maths with dates at a day level, sometimes we need to get a bit cleverer:

=DAYS(end‑date,start‑date) Another way to calculate elapsed days. GOOGLE SHEETS ONLY:A brilliant little function that counts date difference in years, months, days, and variations thereof — the easiest way to calculate somebody's age, for instance. Gives the precise number of years elapsed, expressed with decimal places. Finds a date a specified number of months (to the day) after another date. Gets the last day of a month n months after the date specified — useful for getting month lengths. Calculates the date n working days after the date specified (ignoring weekends (customisable in the .INTL version), and any specified holidays from a range). Calculates the number of working days between two dates (ignoring weekends (customisable in the .INTL version), and any specified holidays from a range).

## Some other useful functions

There are loads of functions beyond what we've already looked at. Some of them we'll investigate later. But here's a few others that it's perhaps worth knowing about. That said, we heartily recommend that you just work your way through the lists of functions to see what's there. You never know when something like ROMAN will suddenly come in handy!

### SUBTOTAL (an alternative to SUM, AVERAGE, etc.)

The standard SUM, AVERAGE and COUNT functions have one particular weakness – they do not take notice of any filters applied to data. The SUBTOTAL function does, however, and is in fact used automatically in the Excel subtotal feature we'll be looking at later.

The name is misleading, as it does a lot more than simply total values. The syntax is:

=SUBTOTAL(function-code, range)

function‑code The type of arithmetic to perform, indicated by a number; for instance: 1 - AVERAGE2 - COUNT3 - COUNTA4 - MAX5 - MIN9 - SUM the collection of cells to be calculated

### Other averages

The AVERAGE function uses the most common form of average: the mean. But the mean is just one form of average. The MEDIAN and MODE functions can be used to find other average measures:

A B C D fx 1 Mean 3 =AVERAGE(A:A) 1 2 Median 2.5 =MEDIAN(A:A) 3 4 Mode 1 =MODE(A:A) 7

### ABS

ABS returns the absolute value of a number, removing any negative sign from it (e.g. -50 becomes 50); positive numbers are unchanged.

This can be useful for calculating the difference between two values, when the sign is not important. For example, if an essay has a word limit of 5,000 it makes it easier to calculate if the wordcount is within an acceptable margin:

A B C D fx Word limit: 5000 Margin: 250 Student Wordcount Difference Absolute difference Anne 5047 47 47 Branwell 4903 -97 97 Charlotte 4981 =B6-\$B\$1 =ABS(C6) Emily 4693 =B7-\$B\$1 =ABS(B7-\$B\$1)

Column C contains both positive and negative values, depending on whether the word count is over or under. Column D displays the absolute value – so only the difference from 5000 is stored, making it easier to work out if the word count is within the acceptable margin of 250. In row 6 ABS is acting upon the value in C, but in row 7 it has been reformulated to incorporate the calculation from C without needing to reference it.

### Rounding

Values are rounded for display, based on the chosen formatting, but the most precise stored value is used for any further calculations. There may, however, be times when you want to work with a rounded value, so several rounding functions are provided. Here's a few, where "value" would generally be a cell reference:

=ROUND(value,places) Rounds the value to the number of decimal places given, using standard rounding rules. Always rounds the value up, to the number of decimal places given.. Always rounds the value down, to the number of decimal places given.. Rounds the value up to the precision given by the factor: e.g. to the nearest 10, or 100, or 0.1, or 0.01... Rounds the value down to the precision given by the factor: e.g. to the nearest 10, or 100, or 0.1, or 0.01... Truncates the value to the number of places given, not by rounding but by simply discarding extra digits. Rounds a value down to the nearest integer below. Rounds a value up to the nearest even integer. Rounds a value up to the nearest odd integer. Gives the remainder when a value1 is divided by value2.

INT and MOD have all sorts of surprising uses. They're particularly handy for converting between non-decimal units, e.g. converting minutes into hours and minutes:

A B C D fx Minutes Hours Minutes left [H]:MM 250 4 10 4:10 =INT(A2/60) =MOD(A2,60) =TIME(B2,C2,0)

### Random numbers

These two random number functions recalculate every time the spreadsheet changes. The ‘randomness’ of the values isn't quite good enough for some specialist uses, but it's good enough to build a set of dice!

=RAND() Generates a random decimal number between 0 and 1. Generates a random integer between the low and high number (negative values are allowed).

### Text

Several functions allow text manipulation, which may be needed if data is not consistent, or is from another source. In the following examples, "text" will usually be a cell reference:

Case change
=LOWER(text)Changes text to lower case.
=UPPER(text)Changes text to UPPER CASE.
=PROPER(text)Changes text to Proper Case.
Trimming, splitting & replacing
=LEN(text)Gets the length of text in characters (including spaces).
=SUBSTITUTE(text,old‑text,new‑text)Searches text for a specified string (old‑text) and replaces it with new-text.
=TRIM(text)Removes any leading or trailing space characters ( ) from text (something you often get with imported data).
Splits text into separate cells at each occurrence of a specified delimiter. split-type is an optional true/false value to determine whether a delimiter with multiple characters is treated as a single entity or as a list of possible delimiters. skip-empty is another optional true/false value to determine whether or not the split should introduce an empty cell when two delimiters appear next to each other.
Joins a range together into a single cell, introducing a specified delimiter at each join. Use "" if you don't want to introduce a delimiter.
=TEXTJOIN(delimiter,[skip‑empty],range)Also joins a range together into a single cell, introducing a specified delimiter at each join. This one let's you decide if you want to skip empty values, and it also has the benefit of working in both Google and Excel!
=LEFT(text,number)Returns the specified number of characters from the left-hand end of text (including spaces).
=RIGHT(text,number)Returns the specified number of characters from the right-hand end of text (including spaces).
=MID(text,start,number)Returns the specified number of characters from the text (including spaces) beginning at the number of characters in from the left determined by start.

The SPLIT and JOIN functions are currently only in Google Sheets. Excel currently doesn't have a splitting function, but there are other ways to split data in Excel, including the following method: 1. Select the cell(s) containing the data to be split
2. Select Data > Data Tools > Text to Columns
4. Choose Finished when you’ve obtained the desired result in the preview
5. Be aware that this method replaces the existing data with the first column of split data. However, we'll see some other methods for splitting later.

#### Joining text

Text strings can be joined together using the ampersand character (&) — we used this method with the conditional functions earlier. Here's another example, where two cells are joined together along with a space character (" "):

A B C fx Surname Forename Name Jones David David Jones Marclay Christian =B3&" "&A3

As well as Google Sheets' JOIN function, both Google Sheets and Excel have further functions for joining texts together, including the TEXTJOIN function from the table above. Both programs also have functions called CONCAT and CONCATENATE, though, bizarrely, they work in opposite ways in each: CONCAT will join together all cells in the range(s) specified. CONCATENATE is a legacy function that could only join individually specified cells. CONCAT will only join two values together. CONCATENATE will join together all cells in the range(s) specified.

I know..! Confusing, isn't it?!  Google Sheets versions of the files are listed below. In each case you will be prompted to make your own copy of the spreadsheet files, for which you'll need to be signed into a Google account. 