Library Subject Guides

- Library
- Subject Guides
- Essential Spreadsheets: a Practical Guide
- 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

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 start with a letter, but may contain numbers
- 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)

- Select the cell or range to be named.
- After selecting the range, enter its name into the
**Name Box**to the left of the formula bar. - 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**.

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:

fx | ||||||

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | VAT rate | 20% | ||||

2 | ||||||

3 | Item | Price | Quantity | Cost | Item VAT | Inc Cost |

4 | A4 paper (5 reams) | 23.99 | 2 | 47.98 | 9.60 | £57.58 |

5 | Pens (box of 10) | 5.50 | 5 | 27.50 | 5.50 | £33.00 |

6 | Pencils (box of 20) | 3.90 | 10 | 39.00 | 7.80 | £46.80 |

7 | Business cards (1,000) | 22.50 | 2 | 45.00 | 9.00 | £54.00 |

8 | Board markers (pack of 8) | 4.75 | 3 | 14.25 | 2.85 | £17.10 |

9 |

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

VATRate

(B1)

(B1)

Price

(B:B)

(B:B)

Quantity

(C:C)

(C:C)

Cost

(D:D)

(D:D)

ItemVAT

(E:E)

(E:E)

Let's start with a simple calculation:

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)

& Google Sheets

fx | ||||||

A | B | C | D | E | F | |
---|---|---|---|---|---|---|

1 | VAT rate | 20% | ||||

2 | ||||||

3 | Item | Price | Quantity | Cost | Item VAT | Inc Cost |

4 | A4 paper (5 reams) | 23.99 | 2 | 47.98 | 9.60 | £57.58 |

5 | Pens (box of 10) | 5.50 | 5 | 27.50 | 5.50 | £33.00 |

6 | Pencils (box of 20) | 3.90 | 10 | 39.00 | 7.80 | £46.80 |

7 | Business cards (1,000) | 22.50 | 2 | 45.00 | 9.00 | £54.00 |

8 | Board markers (pack of 8) | 4.75 | 3 | 14.25 | 2.85 | £17.10 |

9 |

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.

To list all named cells/ranges in the current spreadsheet and to edit/delete them:

- Select
**Formulas > Defined Name > Name Manager**

- Choose the range and hit
**Edit**or**Delete**as appropriate.

- Go to
**Data > Named ranges**to open the 'Named ranges' side-panel

- Locate the named range, hover over it, and hit the
**Edit**pencil

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

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 functionsAlphabetical index of Excel functions
- Google Sheets function listSearchable glossary of Google Sheets functions

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:

- Select the cell in which you wish to enter the function (but don’t start typing anything);
- Select the
**Insert Function**control (theto the left of the formula bar) - this will launch the dialogue;**fx** - 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);
- Once a function has been selected, you can find out more about it by following the "Help on this function" link;
- 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.

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 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:

fx | ||||

A | B | C | D | |
---|---|---|---|---|

1 | Toss | Heads | Tails | |

2 | Heads | 3 | 2 | |

3 | Heads | |||

4 | Tails | First to: | 3 | |

5 | Heads | |||

6 | Tails |

Here are some possible logical expressions about this data that can be either true or false:

Type | Symbol | Example | Logical result | Works with... |
---|---|---|---|---|

Greater than | > | C2>D2 | True | Numbers |

C2>D4 | False | |||

Less than | <
| D2<D4 | True | Numbers |

C2<D4 | False | |||

Greater than or equal to | >=
| C2>=D4 | True | Numbers |

D2>=D4 | False | |||

Less than or equal to | <=
| D2<=D4 | True | Numbers |

C2<=D2 | False | |||

Equal to | =
| A2=C1 | True | Text |

A2=D1 | False | |||

C2=D4 | True | Numbers | ||

D2=D4 | False | |||

Not equal to | <>
| A6<>C1 | True | Text |

A6<>D1 | False | |||

D2<>D4 | True | Numbers | ||

C2<>D4 | False |

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.

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:

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

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Pass mark | 75 | ← Named range for cell B1: "passMark" | ||

2 | |||||

3 | ID | Surname | Forename | Mark | Result |

4 | 10009 | Foreman | Susan | 89 | Pass |

5 | 10034 | Chesterton | Ian | 64 | Fail |

6 | 10072 | Wright | Barbara | 75 | Pass |

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:

Suppose there are 3 possible outcomes to an exam:

Score | Mark |
---|---|

<60 | Fail |

60-80 | Pass |

>80 | Distinction |

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.

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:

Function | Arguments |
---|---|

SUMIF | Range to test, criterion, [optional corresponding range to sum] |

SUMIFS | Range to sum, range to test, criterion, [2nd range to test, 2nd criterion... etc.] |

AVERAGEIF | Range to test, criterion, [optional corresponding range to average] |

AVERAGEIFS | Range to average, range to test, criterion, [2nd range to test, 2nd criterion... etc.] |

COUNTIF | Range to test/count, criterion |

COUNTIFS | 1st range to test/count, criterion, [2nd range to test/count, criterion... etc.] |

MAXIFS | Range in which to find the highest value, range to test, criterion, [2nd range to test, 2nd criterion... etc.] |

MINIFS | Range 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:

fx | |||||||

A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|

1 | Student ID | Year | Major | Exam Mark | Awards | Output: | |

2 | 001 | 1 | Cake | 81 | 1 | ||

3 | 002 | 1 | Chocolate | 76 | 2 | ||

4 | 003 | 1 | Pies | 56 | 1 | ||

5 | 004 | 1 | Cake | 58 | 1 | ||

6 | 005 | 1 | Chocolate | 92 | 0 | ||

7 | 006 | 2 | Pies | 74 | 1 | ||

8 | 007 | 2 | Pies | 72 | 1 | ||

9 | 008 | 2 | Pies | 61 | 2 | ||

10 | 009 | 2 | Chocolate | 81 | 1 |

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.

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:

Condition | Hard-code example | Cell 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. | 2 | A1 |

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 |

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.

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

fx | ||||

A | B | C | D | |
---|---|---|---|---|

1 | Start date | Days | End date | |

2 | Duck conference | 01/01/11 | 8 | =B2+C2 |

3 | Goose conference | =D3-C3 | 7 | 21/01/11 |

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

fx | ||||

A | B | C | D | |
---|---|---|---|---|

1 | Start date | End date | Duration | |

2 | Coot conference | 01/01/11 | 05/01/11 | =C2-B2 |

3 | Moorhen conference | 14/01/11 | 20/01/11 | =C3-B3 |

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:

fx | |||||

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Start time | Duration | End time | ||

2 | Swan conference | 12:00 | 2:00:00 | 14:00 | ← =B2+C2 |

3 | 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**:

- Select the cells you need to format
- Right-click and select
**Format cells...**, or go to**Home > Number > Number format**(dialogue toggle) - Select "Custom" from the
**Category**list - 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.

- Select the cells you need to format
- Go to
**Format > Number > Duration**

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

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

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) |
---|---|

=NOW() | 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:

fx | |||

A | B | C | |
---|---|---|---|

1 | Project | Deadline | Days to deadline |

2 | Cake bake | 31/05/21 | =B3-TODAY() |

3 |

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. |
---|---|

=TIME(hour,minute,second) | 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! |

=YEAR(date‑value) =MONTH(date‑value) =DAY(date‑value) | 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. |

=HOUR(date‑value) =MINUTE(date‑value) =SECOND(date‑value) | Likewise, these pull out the component parts of a time value as integers (0-23; 0-59). |

=WEEKDAY(date‑value,[type]) | 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. |

=WEEKNUM(date‑value) =ISOWEEKNUM(cell-ref) | Returns the week number in the year for the given date. WEEKNUM ticks onto a new number every Sunday, ISOWEEKNUM every Monday |

=DATEVALUE(text) | Creates a spreadsheet date value from a text string that the spreadsheet could read as a date. |

=TIMEVALUE(text) | Creates a spreadsheet time value from a text string that the spreadsheet could read as a time. |

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. |
---|---|

=DATEDIF(start‑date,end‑date,unit) | 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. |

=YEARFRAC(start‑date,end‑date,[type]) | Gives the precise number of years elapsed, expressed with decimal places. |

=EDATE(start‑date,elapsed‑months) | Finds a date a specified number of months (to the day) after another date. |

=EOMONTH(date‑value,elapsed‑months) | Gets the last day of a month n months after the date specified — useful for getting month lengths. |

=WORKDAY(start‑date,elapsed‑days,[holidays]) =WORKDAY.INTL(start‑date,elapsed‑days,[weekend],[holidays]) | Calculates the date n working days after the date specified (ignoring weekends (customisable in the .INTL version), and any specified holidays from a range). |

=NETWORKDAYS(start‑date,end‑date,[holidays]) =NETWORKDAYS.INTL(start‑date,end‑date,[weekend],[holidays]) | Calculates the number of working days between two dates (ignoring weekends (customisable in the .INTL version), and any specified holidays from a range). |

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!

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 - AVERAGE 2 - COUNT 3 - COUNTA 4 - MAX 5 - MIN 9 - SUM |
---|---|

range | the collection of cells to be calculated |

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:

fx | |||||

A | B | C | D | E | |
---|---|---|---|---|---|

1 | 1 | Mean | 3 | =AVERAGE(A:A) | |

2 | 1 | ||||

3 | 2 | Median | 2.5 | =MEDIAN(A:A) | |

4 | 3 | ||||

5 | 4 | Mode | 1 | =MODE(A:A) | |

6 | 7 |

**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:

fx | ||||

A | B | C | D | |
---|---|---|---|---|

1 | Word limit: | 5000 | Margin: | 250 |

2 | ||||

3 | Student | Wordcount | Difference | Absolute difference |

4 | Anne | 5047 | 47 | 47 |

5 | Branwell | 4903 | -97 | 97 |

6 | Charlotte | 4981 | =B6-$B$1 | =ABS(C6) |

7 | 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.

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. |
---|---|

=ROUNDUP(value,places) | Always rounds the value up, to the number of decimal places given.. |

=ROUNDDOWN(value,places) | Always rounds the value down, to the number of decimal places given.. |

=CEILING(value,factor) | 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... |

=FLOOR(value,factor) | 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... |

=TRUNC(value,places) | Truncates the value to the number of places given, not by rounding but by simply discarding extra digits. |

=INT(value) | Rounds a value down to the nearest integer below. |

=EVEN(value) | Rounds a value up to the nearest even integer. |

=ODD(value) | Rounds a value up to the nearest odd integer. |

=MOD(value1,value2) | 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:

fx | ||||

A | B | C | D | |
---|---|---|---|---|

1 | Minutes | Hours | Minutes left | [H]:MM |

2 | 250 | 4 | 10 | 4:10 |

3 | =INT(A2/60) | =MOD(A2,60) | =TIME(B2,C2,0) |

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. |
---|---|

=RANDBETWEEN(low,high) | Generates a random integer between the low and high number (negative values are allowed). |

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). |

=SPLIT(text,delimiter,[split‑type],[skip‑empty]) | GOOGLE SHEETS ONLY: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. |

=TEXTSPLIT(text,column delimiter,[row‑delimiter],[skip‑empty],[match‑mode],[pad‑with]) | EXCEL for MICROSOFT 365 ONLY:Splits text into separate cells at each occurrence of a specified delimiter. It can split on columns and rows and you can split with multiple delimiter strings by putting them in a {"comma","separated","array","like","this"}. skip-empty is an optional true/false value to determine whether or not the split should introduce an empty cell when two delimiters appear next to each other. match-mode is a 1 or 0 to determine whether matches should be case-sensitive, while pad-with defines the text to use on any undefined values within the resulting table (relevant when splitting both columns and rows). |

=JOIN(delimiter,range) | GOOGLE SHEETS ONLY: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. |

Older versions of Excel don't have a splitting function, but there are other ways to split data in Excel, including the following method:

- Select the cell(s) containing the data to be split
- Select
**Data > Data Tools > Text to Columns** - Follow the steps of the dialogue to tell Excel about your data
- Choose
**Finished**when you’ve obtained the desired result in the preview

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.

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 (**" "**):

fx | |||

A | B | C | |
---|---|---|---|

1 | Surname | Forename | Name |

2 | Jones | David | David Jones |

3 | 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.

You can download the above Google Sheets for use in Excel by going to **File > Download**

Excel versions of the exercise files can also be found on university-managed machines at *T:\IT Training\Essential Spreadsheets*

You can map to that drive at home if you use the University's VPN.