We’ll look at the nested ifs and how to enhance if formula using the “AND” and “OR” conditions. So what is a nested if? Nested if or Nested ifs (if there are more than two used in a nested fashion) is when you can use more than one or more if formula within another if formula to arrive at a result. We’ll look at a typical scenario here.
Suppose you are the manager of a large grocery inventory store and you are to prepare a spreadsheet for the daily sales of apples, bananas and oranges. So all you need is to calculate the sales by multiplying the quantity sold by the price of each of the three fruits. You can either do it separately in three steps or you could do it using a single nested if statement.
Now suppose the price of apples were $5 per pound, the price of bananas $6 per pound and that of oranges, $8 per pound using a single nested if statement the sale price can be computed as follows:
=if(A1=”Apples”, B1*5,if(A1=”Bananas”, B1*6, B1*8))
Where the A column contains the name of the fruit and B the quantity in pounds. The formula can go in cell D, starting with D1 and it can be copied to all the other D cells spanning the length of the spreadsheet.
A little more explanation of the formula will help add clarity to our computation.
Note that the first if first checks for if the item is an apple. If it is true then the sale price is calculated by multiplying the quantity by 5 (the price of apple). If is false then the item could either be bananas or oranges. So the false component of the first if, now carries the second if which in turn can take three arguments. The first which checks if the fruit is banana and if so computes the sale price as quantity times 6. If the fruit in the second if statement is not a banana then obviously it has to be an orange and therefore the false portion i.e. the part where we need to mention criteria if the statement was false automatically computes the sale price for oranges by multiplying the quantity by 8.
We could extend the if’s even further but for sake of clarity the more ifs we were to use the more careful we need to be about the criteria and the subsequent computations.
You can further enhance the if formulae using “AND” and “OR” conditions. Now suppose you want to calculate only the sale prices of apples in the state of Colorado then the above formula can be modified as follows to give us the desired result:
=if(AND(A1=”Apples”,C1=”Colorado”), B1*5) where the column C contains the states where the sale was made. Also notice that we did not include the parameter for the negative result as we are only concerned with the sales of apples in Colorado. Note that you can include more than two conditions in the AND clause. In fact you can up to 255 entries.
The “OR” condition can be used in a similar fashion. Just replace the AND in the above example with the “OR” and you are all set.
Note that you can also insert the “if” formula inside a cell using the menu. IF can be used with both text and numeric data.
Nested If Statements can be frustrating and difficult to first understand. If you need one-on-one Excel help please contact Christopher at the corporate office in the Irvine area of Orange County California and he can determine your needs, provide an estimate, and provide the paid services should you decide you need the help. Or you can e-mail your questions, and we will answer those right away. We are here to help.