AbleOwl

Tip:932 Use LET to make a formula easier to understand.

The task below is to extract into column L the middle name of the text in column K.

You can use a combination of MID and FIND functions to do that as in L15. As per the previous tip, you can make the formulas easier to follow by splitting the formula onto multiple lines with Alt+Enter.

MID returns a number of characters from a starting position. For example, MID("abcdef",2,3) returns bcd being 3 characters starting from position 2.

FIND searches for text and returns the position of the match. For example, FIND("c","abcdabcd") returns 3, the position of the first c. There is an optional third argument, which is the position to start searching from. For example, FIND("c","abcdabcd",4) returns 7, the position of the first c found from a search starting at position 4.

Unless you know FIND well, the formula is hard to understand. With LET, a relatively new function, you can make the formula easier to understand.

Let's start with simple examples of LET. The examples below are from the Excel help.
You can store values into what it calls variables. Example 1 stores 1 into a variable named x. The last argument is called the calculation, which in this case is the calculation of x+1. The result returned is 2.

Example 2, has two variables named x and y. LET stores 1 into each variable. The calculation is x+y and, so, the result is 2 again.

You can have up to 126 variables. In L16 above, there are three: FirstSpace, SecondSpace and MiddleName.

Into FirstSpace, FIND puts the position of the first space.
Into SecondSpace, FIND puts the position of the second space.
Into MiddleName, MID returns the text that starts from 1 character after the FirstSpace and which is SecondSpace-FirstSpace characters long, that is, the length of the middle name.

For the last argument, there is no further manipulation required; just return the MiddleName.

You could remove the two arguments that contain variable name MiddleName, but doing it this way makes it clear that the formula returns the middle name.

Excel file:
https://github.com/adminableowl/ExcelFreeTip932.git

Make your nested IF formulas easier to understand
A formula like that below is hard to follow. It just wraps around in the Formula bar. It's a typical formula with nested IFs.

=IF(AO505=0,"N/A",IF(OR($AP$505="Tiles supplied by customer",AO498="No"),"Coping tiles supplied by customer.",IF($AR$505,"See Options", IF(OR($AP$505="", $AP$505="Not selected"), AP507,"Coping tiles supplied from "&AP505&" - "&AQ505))))

You can rearrange the formula as below to make it easier to follow. IF has three arguments: (1) Test (2) Value if test is true (3) Value if test is false. Note the layout below where the second argument is below the IF and indented by 4 spaces. The third argument is on the next line down and also indented by 4 spaces.

With the indentation below, you can see the four levels; the first IF contains another IF, which contains another, which contains another. The logic flow is now much easier to follow.

=IF(AO505=0,
"N/A",
IF(OR($AP$505="Tiles supplied by customer",AO498="No"),
"Coping tiles supplied by customer.",
IF($AR$505,
"See Options",
IF(OR($AP$505="", $AP$505="Not selected"),
AP507,
"Coping tiles supplied from "&AP505&" - "&AQ505))))

In creating a formula, to start a new line, press Alt+Enter
To indent, simply type spaces. We recommend you enter 4 spaces to indent a level.

Excel tip #928 - Swings and roundabouts

Take a look at the image for this post. Each of the columns rounds the first Values column ... but the answers aren't all the same! See the orange rows.

So what's going on here?

Excel's ROUND() formula uses arithmetic rounding - that's the type we did in school where 5 was always rounded up. So, ROUND(1.125,2) yields 1.13.

The cell formatting system (something like: #, # #0.00;- #, # #0.00) also uses arithmetic rounding.

However, users of VBA will know that its Round() function uses bankers rounding - where 5 is rounded to the nearest even number. So, Round(1.125,2) in VBA would return 1.12.

For statisticians, the method of rounding is important, especially to avoid data being skewed by always rounding 5 up. Meteorologists, for example, have long debated how to round, say, fractions of rainfall.

For the rest of us, it's not so important ... as long as we're consistent. The problem comes when we're comparing data that has come from different sources (which use different rounding methods). And it's more common than you might think, because PowerQuery uses bankers rounding. Use PowerQuery to read invoices paid and Excel's ROUND() function to compare with invoices sent and, boom, you've got mysterious 0.01 differences.

If you know that you're going to need bankers rounding in your Excel formulas, then this one is often used:

=ROUND(cell,2)-(MOD(ROUND(cell*10^(2+1),0),20)=5)/10^2

This is the one used in the last column of the image above.

Alternatively, you could create your own VBA function:

Public Function BROUND(d As Double, n As Long) As Double
BROUND = Round(d, n)
End Function

For PowerQuery, always set the rounding method to match what you intend to use in Excel. There are a number of options, here's one:

Number.Round(1.125, 2, RoundingMode.AwayFromZero)

Excel Tip #927 - Unreadable IFs

It doesn't take much for an IF statement to become cumbersome and difficult to read. This one, for example, just writes "1st", "2nd" or "3rd" for the results of a running race:

=IF(M15=1,"1st",IF(M15=2,"2nd",IF(M15=3,"3rd","")))

The more embedded the IF conditions, the harder it is to see which condition yields which result.

In those cases, the SWITCH function could be of help to you. Here, you just enter each condition for a particular cell and the desired return value. So the equivalent 1st, 2nd, 3rd function would look like this:

=SWITCH(M15,1,"1st",2,"2nd",3,"3rd")

The only issue with SWITCH is that it can only handle exact matches; that's to say you can't use operators like 'greater than', etc.

Suppose you also gave runners a medal, ribbon or certificate if their times were above certain values. This is where the IFS function comes to the rescue. It works in a similar way to SWITCH but you can apply operators:

=IFS(L15