Excel Tip: Applying multiple criteria in an IF formula

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

So, let's say the following cells are populated as below:

A1="Red"

A2=55

A3=400

So,

=AND(A1="Red",A2>30,A3<500)

will return TRUE as all conditions individually would return TRUE, whereas

=AND(A1="Red",A2<30)

will return FALSE, because one of the conditions would return FALSE.

However,

=OR(A1="Red",A2>30,A3<500)

will return TRUE as at least one condition individually would return TRUE, but

=OR(A1="Red",A2<30)

will also return TRUE, for the same reason.

You can create combinations of the two as well, by use an AND function as one of the arguments in an OR function, or vice versa.

So, for example:

=AND(A1="Red",OR(A2=55,A3=27))

works as follows, A1 must equal "Red" AND at least one of A2=55 or A3=27 must be true.

In this case it would return TRUE, whereas

=AND(A1="Blue",OR(A2=55,A3=400))

would return FALSE because A1="Blue" isn't true.

You can actually create quite complex rules using these two functions.

These do not, however, allow you to apply different criteria based on the results of other criteria. For that you need nested IF functions, which I will cover in a future post - maybe even next week.

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

Excel Tip: Highlighting a specific row or column (even after inserting and deleting)

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

Excel Tip: Converting text to a number

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

Excel Tip: Average with blanks

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