Participation Project #06 - Formulas & Functions II

In this project, you will be creating 3-D formulas, using the IF function, the AND/OR functions, and creating nested IF statements.

3-D Formulas

3-D formulas are formulas that reference cells from multiple worksheets in the workbook. Using the names of the spreadsheets we’ve been working with, a 3-D formula might reference one or more cells from the Coal Mined tab as well as one or more cells from another worksheet, like from the Prices tab. Your formulas always start with an equal sign – this is what tells Excel it needs to calculate something instead of just inputting text. The easiest way to do this is to click on the cells you need on the first tab (hint: CTRL+click will select multiple, non-contiguous cells), click on the secondary tab, and then hold down control while you click on the cells you need on that worksheet. You should not try to type these in manually as it's very easy to mistype the equations. Excel will enter the names of the cells in the formulas the way it wants them referenced. Rather than clicking back on the original sheet, just hit “enter” when you’re done.

IF Statements

IF statements are one of the built-in functions in Excel. You can access this through the insert function wizard (the f(x) on the left side of the text bar). IF statements are made up of three parts:

1. The conditions that you are testing.
2. What happens if your conditions are true.
3. What happens if your conditions are false.

If you are given the IF function and it’s already written for you, like it is in Step 4a.i, your best bet is to copy and paste it directly from the PDF into the spreadsheet. However, it’s important that you understand how the IF functions work. The first part of the formula is the criteria that you’re testing. For example, let’s say you want to know if the percentage of the total coal mined for each county is greater than 10%. If it is, we want the formula to say Yes. If it’s not, we want the formula to say No. So how do you start? The first part – and usually the hardest part – is figuring out what to put for the criteria. In the example I’m using, we would want to reference the percentage of coal mined, which can be found in Column H.

We’re looking at the percentage for each individual county, which means that in our criteria, we’re looking to see if the value for column H for that row is greater than 10%. The first thing to remember here is that in Excel, the numbers are formatted as percentages, but they actually entered into the worksheet as decimals, so when we put the percentage into the formula, it’s going to go in as 0.10 instead of 10% (10/100 = 0.10). In our example, the first part of the IF function looks like this:

You’ll notice that Excel doesn’t put the cell reference in as H4, but rather as [@[Pctg of Total Mined]]. This is because the cell is part of a table and Excel references those differently. You could still type in H4 in the Logical_test box and it would work. The second part is the part of the criteria you’re testing, i.e., whether that cell is greater than 10% (0.10). If you look at the image above, you’ll see that after the criteria I typed in, it says “= FALSE.” That tells me that in this particular cell, the value does not meet the criteria that I am testing for – the logical test is FALSE – and therefore it should return the Value_if_false rather than the Value_if_true. If we were looking for values less than 10%, the opposite would be true.

The next parts are what happens if it’s true (we can type any number of things here: true, correct, yes, etc.) and what happens if it’s false (false, incorrect, no, etc.). Excel will return whatever information you put in. It’s important to note that if you are putting in text rather than referencing another cell or formula, you have to enclose the text in double quotes. This is true of any function in Excel.

The other important thing is that if you have completed your formula correctly, it will show you the result of your formula at the bottom left-hand corner. In our example, we are trying to determine if the value in that cell is greater than 10%. It is not (the value is 2.4% or 0.024 when expressed as a decimal), so the formula evaluates to the Value_if_false, which I have entered as No. Above, you’ll see where it says, “Formula result = No.” Hit enter, and it will input the formula into the cell you selected in the worksheet. The end result should look like this:

The commas essentially tell Excel that each of the different pieces of the formula are a different argument – there were three arguments in the formula (logical_test, value_if_true, value_if_false) – and the commas separate each of these.

Question Guidance

Question 4 creates an IF statement with multiple criteria for Excel to evaluate. The presentation gives you a good explanation of these, and the video walks you through how to use them.  In short:

• The AND() function requires all logical conditions to be true to return TRUE.

• The OR() function is opposite of the AND() function.
• If one or more conditions is true, then the result is TRUE
• If all conditions are false, then the result is FALSE

In short, the IF function just gives a binary answer - it’s either true or false.  The AND() & OR() functions introduce a little complexity - all items have to be true with AND to result in a true return (ie what is in the true box is displayed), or with or, only one item has to be true in order for the true condition to be displayed.

Again, with these examples, you’re simply to copy and paste from the instructions.

In question 5, you’ll need to create your own IF statement. Use the information above to write the formula; if you need help, please log onto one of this week’s Zooms or send me an email.

Question 6 is what is known as a nested IF statement. Sometimes we may have more than two possible results – for example, we may want to label something as low, medium, or high. Since an IF statement returns only two values, we can solve this problem with a nested IF statement. That means that instead of putting a static argument for the Value_if_false, we start another formula for Excel to evaluate in the section for Value_if_false. You should type these into the formula bar rather than trying to use the Insert Function Wizard.

To input a second IF statement in the formula, you don’t need another equal sign, just start with the function name (for nested IF statements, the function will be IF). In the formula on the PDF, our options are Low, Moderate, and High. The first IF statement evaluates if the value meets the criteria for High – the nested statement says if it’s not High, then to label it as Moderate if it meets the criteria for Moderate. If it doesn’t meet either of these criteria, Excel will label it as Low.

You have to be careful with nested IF statements, because you want to make sure that the first one is exclusive. You basically want to make sure that whatever your first argument is, it will not include values that also meet the second and third criteria. In this scenario, if you started with the formula so that anything greater than or equal to 75 was listed as moderate, it would also include values greater than 100. Because they met the criteria, those values would be evaluated as Moderate rather than High. Once a formula has been assigned a value, it will not be available to be evaluated for another criteria.

Also, if you’re given information like “between 19 and 37,” but you’ve already accounted for anything less than 18, you don’t have to worry about those being assigned something else – which means you don’t have to worry about the first part of that range. To write that formula, you’re going to write your first IF statement for any values less than 18, but the second can be written for anything less than 37 because you’ve already assigned “Top Third” to the numbers less than 18. Hopefully that makes a little bit of sense, but please ask questions if it doesn’t.