Excel Formulas – Page 2 – STL

If you’ve used Excel for some time, you may want to learn more about the different logical functions such as IF, AND and OR. This post describes how these functions can be combined to perform very useful composite logical operations.

IF Function

The IF function will carry out one of two actions.

One action is carried out if a specified test is true.
The other action is carried out if the specified test is false.

If the function is typed into a cell, the function starts with an equals symbol and then the letters IF, then an open bracket, followed by the three elements test, true action and false action separated by commas and followed by a close bracket.

The function looks something like =IF(TEST,TRUE,FALSE).

For example, suppose the TEST part is A1>100 where cell A1 contains a number of sales.

The TRUE part is “WELL DONE”, and the FALSE part is “Try Harder”. So the complete function looks like =IF(A1>100,”Well Done”,”Try Harder”).

The functions displays either “Well Done” or “Try Harder” in the cell, depending on whether the number in A1 is more than 100 or not. If you type the IF function into, for example, cell A2, you can then try different numbers in cell A1, above or below 100, and you’ll see either of the messages displayed in cell A2.

AND Function

The AND function will display either TRUE or FALSE in the cell containing the AND function.

The function starts with the usual equals sign and then AND followed by brackets which contain one or more tests separated by commas. If all the tests are true, then the cell shows the value TRUE. If any of the tests are not true, then the cell shows the value FALSE.

Suppose in our above example cell B1 contains number of customers. Suppose you type in this formula to cell B2 =AND(A1>100,B1>5) and press the Enter key and look at the result. Provided the number in cell A1 is more than 100, and the number in cell B1 is more than 5, then the function shows TRUE. Try different values in A1 and B1 to confirm that cell B2 only shows TRUE if both tests are true, and FALSE if either or both tests are not true.

OR Function

The OR function will also display either TRUE or FALSE in the cell containing the OR function.

The function starts with the equals and then OR followed by brackets which contain one or more tests separated by commas. This time if any one test is true, then the cell shows TRUE. If all tests are not true, then the cell shows FALSE.

So suppose you type this formula in cell C1 =OR(A1>100,B1>5) and press the Enter key. If you try different values in cells A1 and B1 you’ll find that cell C1 which contains the OF function shows TRUE if either or both cells contain numbers over the set values.

Combining IF with AND function

Suppose we modify our IF function. We now want to show “Well Done” only if sales in A1 are more than 100 and customers in B1 are more than 5. In other words our IF function will need to have two tests true to show “Well Done”.

We can do this by combining an IF with an AND function. So in cell D1 type in the formula =IF(AND(A1>100,B1>5),”Well Done”,”Try Harder”) and press the Enter key. You can see that the AND function is now the test for the IF function, and for the test to be true both numbers in cells A1 and B1 need to be more than the set amounts.

Combining IF with OR Function

Suppose we again modify our IF function. This time we want to show “Well Done” if sales in A1 are more than 100 or customers in B1 are more than 5. In other words our IF function will now need to have only one test true to show “Well Done”.

We can do this by combining an IF with an OR function. So in cell E1 type in the formula =IF(OR(A1>100,B1>5),”Well Done”,”Try Harder”) and press the Enter key. Now you can see that the OF function is the test for the IF function, and for the test to be true either the number in cell A1 or the number in cell B1 needs to be more than the set amount.

In conclusion the IF function is one of the most important logical functions in Excel but you can combine it with AND and OR functions in different ways to create even more powerful logical tests.

A really effective way to learn more about advanced Excel and it many very useful features is to attend a training course. Then you can really boost your skills in using Excel. https://www.stl-training.co.uk/excel-2010-advanced.php

The convert function is used to change data from one unit type to another in Excel.

It’s often used to convert dates, but it can do so much more as there is a range of conversion units that you can use to make your life easier.

The convert function is an engineering function. In Excel 2010 it is found in the Formulas section of the ribbon as follows; More functions>Engineering>convert.

convert-formulas-excel-course — The convert icon is located in the Formulas tab.

Converting dates and times
Dates and times often require converting and Excel can do this for you automatically, you just need to set it up, to do so.

For example, if you add or subtract calculations between two dates, Excel will automatically give the result in days unless you tell it otherwise. So you need to perform another calculation to get the result in the format you require (years, months or hours).

For example, a call centre keeps a worksheet with the start and end times and dates for calls received. The manager wants to find out how long each call takes. To show the duration in the most appropriate unit, the answer needs to be presented in minutes rather than hours, however, the format of the data means that the result is shown in days. So the manager can use the following convert formula to show the results in minutes instead:

=CONVERT(G5,”day”,”hr”)

Converting weights
An example would be converting ounces to grams in sales data for a family butcher, and he uses the the following formula:

=CONVERT(A1,”ozm”,”g”)

Length conversion
An online fabric shop may need imperial and metric measures for selling material. In the UK, many people still ask for a yard of fabric rather than a metre, and data can be stored on a stock spreadsheet easily in both units. This way those people who think in yards can see the yards, those who want metres can see metres. The following formula would achieve this:

=CONVERT(G5,”m”,”yd”)

There are more units that you can convert. To explore this function and get more out of Formulas consider attending one of our Excel courses. Find out more on https://www.stl-training.co.uk/excel-2010-intermediate.php