From the Not Just Numbers blog:

In last week’s post I discussed AND and OR, as ways of combining criteria in an IF statement. These are perfect if each test that you wish to apply is independent of the others.

What about, if you wish to apply additional tests based upon the results of other tests, resulting in more than to possible outcomes?

In real life this might be something like:

“If it is sunny today, then if all of the family agree we will go for a walk, but if they don’t, we will go for a picnic – however, if it is not sunny, then if there is a football match on tv, we will watch that, otherwise we will watch a film.”

This kind of thing is easier than you might think, if you keep your head!

If you need a refresher on the IF statement before we start then visit my earlier post:

EXCEL TIP: The IF Statement made simple

The answer to this kind of problem is the use of something called Nested IF statements. In Excel, we can ‘nest’ functions inside other functions. This means using a function as an argument inside another function. To address the kind of problem above, we can nest IF functions inside of other IF functions to produce the logic that we want.

These can lead to pretty complicated-looking formulae when you are finished, but need not be complicated to build if you take them step-by-step.

For our example let us say that we define the following cells as range names:

A1 as sunny

A2 as familyagreewalk

A3 footballontv

Each cell can contain either Y or N, for Yes or No.

We could just use the cell references but it will be easier to see the logic of the formula with the defined names.

I sometimes find it useful to do one IF statement at a time, placing dummy answers where I will later place a further if function. Taking this approach we can apply the first text as follows:

=IF(sunny=”Y”,”AAA”,”BBB”)

This will simply return AAA if it is sunny, or BBB if it is not. We can then replace “AAA” with the test that we wish to apply if it is sunny, which is IF(familyagreewalk=”Y”,”Walk”,”Picnic”), this makes our formula:

=IF(sunny=”Y”,IF(familyagreewalk=”Y”,”Walk”,”Picnic”),”BBB”)

We can the replace “BBB” with the test that we want to apply if it is not sunny, i.e. IF(footballontv=”Y”,”Football”,”Film”), so our formula becomes:

=IF(sunny=”Y”,IF(familyagreewalk=”Y”,”Walk”,”Picnic”),IF(footballontv=”Y”,”Football”,”Film”))

We can keep adding nested IFs in this way. We may want to replace “Film” with a rule that determines what kind of film we will watch, for example.

Since Excel 2007, you can actually have up to 64 Nested IFs in one function, although your formula would get pretty insane well before that! Excel 2003 only allowed 7, which is already starting to get a little too complex. Usually when you have more than around 4 or 5, there is usually a better way of doing it. Lookups can be helpful, for example.

You can comfortable build up 4 or 5 if you use the approach above. Trying to go straight into writing the whole function, you can very quickly get your brackets in a knot!

Good Luck!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

This week’s post mostly applies to using the IF function, so if you don’t already know how to use this incredibly powerful function, I would recommend that you read this post from 2010, which is still one of my most popular posts every month:

EXCEL TIP: The IF Statement made simple

If you already know how to use the IF function but want to apply multiple criteria, read on.

If you wish to apply more than one condition to your IF statement then you need to decide how you want to combine the multiple criteria. The two main options are:

1. All conditions must be true – this uses the AND function
2. Any of the conditions can be true – this uses the OR function

From the Not Just Numbers blog:

I was asked this question in response to an earlier post and thought it was worth sharing my response as it has many applications.

The reader asked how to highlight a specific row number (in red, say), without the highlight moving when you insert or delete rows.

This might be to highlight when you have exceeded a target on a list for example.

Say, we want to highlight row 101, this would typically be the 100th record in a list with headers.

If we simply place a red fill on row 101, this will move to row 100 if we delete a row above it, or row 102 if we insert one.

How do we get it to stay on row 101?

The answer is to use Conditional Formatting.

First of all we need to highlight the range that we wish to apply the conditional formatting to. This could be the whole sheet, but this uses unnecessary resource that might be important in a bigger spreadsheet. A better approach would be to only apply the conditional formatting to the cells that we need to.

The width of our highlighted range should be how far across that we want the highlight to go (probably to the last column that we will be using), let’s say column H.

The height needs to allow for the maximum amount of deleting rows that might happen as the range will reduce when we delete rows in it (but expand when we add them). Unless we expect to do a huge amount of deleting and very little inserting, then 1,000 rows should be more than adequate.

So, let’s highlight the range A1:H1000, then click Conditional Formatting on the Home Ribbon, and New Rule.

From the list of options, select “Use a formula to determine which cells to format”.

In the formula box, type:

=ROW()=101

Click the Format button and choose the format for the highlighted cells, e.g. a Red Fill.

Click OK and you’re done. Row 101 will be highlighted in red up to column H, and this highlighting will stay on row 101 after inserting and deleting rows!

You can do exactly the same thing with columns, using =COLUMN()=x. Note that x is a column number rather than a column letter, so if we wanted to highlight column H, it would be =COLUMN()=8.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Sometimes the information you need is not in the format you want it in.

In Excel, this problem often manifests itself as numbers formatted as text.

This is a problem, as when you try to do any kind of calculation with the number, you will get an error.

Excel’s General number format will normally format a number that looks like a number, as a number, but this might not be the case for imported data, or if the number is part of a text string.

Fortunately, Excel (as usual) has an answer.

We can use the VALUE function to convert the text to a number.

So, for example, if cell A1 contains a troublesome number that is being read as text, that you wish to multiply by 2, but =A1*2 is returning an error, then:

=VALUE(A1)*2

should solve your problem.

A more common problem is where the number may be contained within a text string.

For example if A1 contained a number prefixed by a letter then we could use text manipulation to strip out the bit of text that contains the number – then use VALUE to convert it to a number.

So, if A1 contains text such as B230, and we wish to multiply the 230 by 2, we could use:

=VALUE(RIGHT(A1,LEN(A1)-1))*2

This earlier post explains the use of RIGHT and LEN. Essentially, RIGHT(text,x) returns the x rightmost characters from text, and len(text) returns the length of text (in characters). So, RIGHT(A1,LEN(A1)-1) returns all but one of the characters in A1, leaving out the leftmost character.

So, if A1=B230, then LEN(A1)-1=3, so RIGHT(A1,3)=230. This, however, is a text string, so we use the VALUE function to convert it to a number, before we multiply it by 2!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Just a quick post this week on a quite subtle issue.

If I can do a calculation using basic functions, I tend to do that rather than worry about finding a more advanced function to do the same thing.

For example, I might work out an average using something like:

=SUM(A1:A3)/3

instead of using Excel’s AVERAGE function.

An emailed question I received last week prompted me to re-think that approach.

The questioner had a problem with an average calculation, and wanted a formula to calculate an average, taking into account how many numbers were actually in the range. i.e. ignoring text and empty cells in both the numerator and the denominator.

My first instinct was to expand my existing approach. The SUM function will ignore anything that isn’t a number anyway, so my numerator was fine.

The denominator would also need to ignore text and blanks too, for the average calculation to be correct, so I came up with:

=SUM(A1:A3)/COUNT(A1:A3)

NB: The COUNT function counts the number of cells containing a numeric value in the range.

This works, but a little more digging and I discovered that the AVERAGE function does this by default!

So the far simpler answer is:

=AVERAGE(A1:A3)

I think that’s what’s called re-inventing the wheel!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Before we get into this week’s post, just a quick reminder that you can still get 20% off Mynda Treacy’s Excel Dashboards course until Thursday, so don’t dither any longer if you want to partake!

This week I want to talk about entering multiple lines of text in one cell.

I’m not talking about wrapping text which simply runs the text over multiple lines when it doesn’t fit in the width of the cell (this can be enabled for any cell by selecting the cell and clicking Wrap Text on the Home ribbon). I’m talking about choosing where the line breaks are to show different bits of information on different lines (in the same cell), or to break longer text into paragraphs,

This can be particularly useful in mail-merge data, for the body of an email, for example.

There are two different scenarios where you may want to do this which need addressing slightly differently.

The first, and simplest, scenario is when you are entering the text directly into the cell.

In this case, you can simple press Alt-Enter whenever you need a line break (in the same way that you might hit Enter in a Word document). When you do this, notice that Excel has automatically switched on Wrap Text for the cell (Wrap Text on the Home ribbon will be highlighted when the cell is selected).

The second scenario is when you are generating the text using a formula. Here you need to refer to the line feed character by its code.

We can use the CHAR function to refer to a character by its character code,

CHAR(10) is the Line Feed character.

We can build up the text in our cell using the & character to join the text together.

So, say that we have the text for paragraph 1 in cell A1, and the text for paragraph 2 in cell B1, then we can join them together using & as follows:

=A1&B1

However, the text from both cells will simply by joined together, without even a space to separate them, never mind a line break.

However, we can insert the Line Feed character as follows:

=A1&CHAR(10)&B1

At first this might appear to look exactly the same, however the line feed is there and will be used in a mail merge, for example.

It will have no effect on how it is displayed in Excel, until we switch Wrap Text on for the cell (it is not automatic in this case). We can now see that a line break has been forced where we put CHAR(10).

You can use this to create quite complicated sections of text, built up from multiple paragraphs selected using IF statements and or lookups.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

I hope everyone who celebrates it has had a good Easter break. I’ve just got a quick post to ease you back into work this week.

Have you ever wanted to show the weekday’s name in a date? Or, show the name of the weekday for any particular date. Excel makes this much easier than you might think.

You can do this using number formats.

If you right-click a cell with a date in it and select Format Cells, Number Format, then select Custom, you can design your own number format by typing into the Type: box.

A typical UK date format, might be dd/mm/yyyy. This will show the day and month as a two digit number and the year as a four digit number.

So, for example, the 7th April 2015 would be 07/04/2015.

The day, could be shown as ddd or dddd, and these work as follows:

ddd gives the three letter version of the day, e.g. Tue

whereas dddd gives the full name of the day, e.g. Tuesday

The month works similarly, so mmm would return Apr and mmmm would return April.

We could type a custom format such as:

dddd, dd mmmm yyyy

This would show the same date as:

Tuesday, 07 April 2015

Alternatively, you could simply show the day itself by using a format as:

dddd

Using this format, the cell will still hold the full date, but will just display the day, e.g. Tuesday.

You can also use this format in the TEXT function, to return the name of the day as text in another cell.

Say that the date (07/04/2015) is in cell A1, then:

=TEXT(A1,”dddd”)

will return the text Tuesday.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

We’re all learning, all of the time, when it comes to Excel. This week, I thought I would cover a topic I’ve covered before, but from a different angle.

Using the dollar sign in an Excel formula is one of those simple, but powerful, things that I constantly find that people have different layers of understanding of.

Part of the problem, is that once we think we know something, we are much less likely to learn any more about it.

I see this regarding the dollar sign all of the time, and although there is nothing complicated about it, I typically see four different levels of understanding. There may even be a fifth, as I’m still learning too!

As this is such a powerful tool, you might want to check to make sure that you’re not missing any of them.

Level One

“I’ve seen them in other people’s formulae, and wondered what they did.”

I would certainly recommend reading my earlier post on them, as you are missing out on a really powerful (and simple) tool.

Level Two

“I use them to fix a reference to a cell, for when I copy a formula”

Yes, you can fix a cell by using two dollar signs, e.g. $A$1, but did you know that each dollar sign has its own purpose. The one before the column fixes the column, and the one before the row fixes the row.

So $A1 will always refer to column A (and the row will change relatively), whereas A$1 will always refer to row 1 (and the column will change relatively).

Again, I would recommend reading my earlier post for a fuller explanation.

Level Three

“I use them in almost every formula to fix cells, rows or columns. I don’t know what I’d do without them!”

I was at this level for years. In fact I was at this level when I wrote the blog post referred to above! Then one day, I was looking over a client’s shoulder and saw the dollar signs changing at the press of a key. It was one of those “Hang on! Show me what you did there.” moments.

Using the function key F4 while on a cell reference in a formula (whether it already has dollars or not), will toggle through all of the options, e.g. if the reference is A1, then repeatedly pressing F4 will have the following effect:

First press:   $A$1

Second press:   A$1

Third press:   $A1

Fourth press:   A1

Keep pressing and it will go through that loop each time.

When I discovered this, I wrote a follow-up post. Keep learning!

Level Four
“I press F4, probably more than any other key!”

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

Although it is worth noting the dangers of hiding rows (see this earlier post for a particularly embarrassing example), they can be useful. If you do use them, you may want any totals on the sheet to exclude the hidden rows, so that the data presented is consistent (i.e. so the total is the total of the numbers you can see).

Fortunately, Excel provides a useful tool for this.

Normally we use the SUM command to add the values in a range, so if we wish to add up column A, rows 1 to 100, then we would use:

=SUM(A1:A100)

This is great, unless we hide row 30 for some very good reason!

If someone was to manually check your sum, they would find that the total wasn’t correct (given the numbers they could see that it was supposed to be adding up). This is because the SUM command will include the hidden row.

We can, however, use a different command to do this that will exclude the hidden row. This command is SUBTOTAL.

=SUBTOTAL(109,A1:A100)

will do the same as the SUM command above, but ignore the hidden rows in the total.

I know you’re thinking “Where did that 109 come from?” (like many a Battle of Britain Spitfire pilot!)

Well in this case it’s not the Luftwaffe, but part of the workings of the SUBTOTAL function.

This first argument can be one of 22 numbers in the ranges, 1 to 11 and 101 to 111.

The numbers 1 to 11 cause the function to mimic the following Excel functions:

1 AVERAGE

2 COUNT

3 COUNTA

4 MAX

5 MIN

6 PRODUCT

7 STDEV

8 STDEVP

9 SUM

10 VAR

11 VARP

So,

=SUBTOTAL(9,A1:A100)

would work almost identically to our SUM function, except for the following two subtle differences:

1. It will exclude rows hidden by Autofilter
2. It will also exclude any other SUBTOTALs in the range

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

From the Not Just Numbers blog:

In last week’s post about an online random name generator, I mentioned that you could use the RANDBETWEEN function if you wanted to generate random numbers instead.

I was also surprised to realise that I hadn’t already written a post on this that I could link to – so I am remedying that this week.

The RANDBETWEEN function generates random whole numbers and is really simple to use. Its syntax is as follows:

=RANDBETWEEN(bottom,top)

where bottom and top are the lowest and highest numbers that you want to generate.

So, you could generate a random number between 1 and 10 using:

=RANDBETWEEN(1,10)

or between -100 and 100:

=RANDBETWEEN(-100,100)

or if you want to generate a decimal (2 decimal places) between 1 and 10, you could use:

=RANDBETWEEN(100,1000)/100

Note that the formula will recalculate (and therefore generate a new number) every time Excel calculates. With automatic calculation on, this will be every time you edit a cell on the sheet.

Often I use the function to generate a series of random numbers to use as sample data, in which case I will usually copy them and paste them as values over the formulae to fix the numbers.

You could also combine the function with INDEX to select random items from a list.

=INDEX(A1:A10,RANDBETWEEN(1,10))

will randomly return the contents of cells A1 to A10.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.