Writing Formulas in Excel Can Be Facilitated by Using Flow Charts

IF STATEMENTS

Excel has a number of built-in functions that we will be employing. Probably the most useful single function is the IF statement that allows you to create equations that determine what result will occur based upon decision criteria. The general form of the function is

=IF(logical test, value if true, value if false)

The logical test is generally used with numerical values, but can also be used with text and other functions. If the logical test is true, then the calculation/instructions/value placed following the first comma is executed, whereas if the logical test is false, the calculation/instructions/value following the second comma is executed. The value if true (false) positions can contain additional functions, including more IF statements. Beginning with Excel 2003, the limitation of nested functions has been expanded to sixty-four. The term “levels” refers to nested functions within sequential functions, not the total. For instance, there could be 63 nested functions within the value if true portion of the IF statement as well as 63 nested functions within the value if false portion of the income statement and everything would still work. Both would be at the limit of 64 when the original IF statement is counted. As a practical matter, if you are going to use a lot of nested functions, you’ll probably want to separate them out into subsets at some level. The reason is due to the difficulty of editing the equations and following the logic when an equation becomes exceedingly long.

Writing formulas in Excel can be facilitated by using Flow Charts. The following Flow Charts should help with the Exercise2-IFstatements spreadsheet. The solution equation is posted with the spreadsheet, but you should try doing it yourself beginning with the second record (Advanced Micro).

Practice 1

With the data below, do the following: If the value is greater than $80.00, insert a value of $80.00 in Column E; otherwise, put in the original value.

Yes

The first box is the IF statement condition. The second part of an IF statement reflects what to do if the condition is “true” (Yes). The third part of the IF statement reflects what to do if the condition is “false” (No).

The equation in cell E4 would be as follows: =IF(d4>80,80,d4)

The equation could then be copied to cells E5 to E15

Practice 2

With the data below, do the following: If the value is greater than $80.00, insert the value of the the previous cell (in column E); otherwise, put in the original value.

Yes

The equation in cell E4 would be as follows: =IF(d4>80,e3,d4)

The equation could then be copied to cells E5 to E15

Practice 3

With the data below, do the following: If the value is less than $40, insert a value equal to (3*value-5.5); otherwise, put in the original value.

Yes

The equation in cell E4 would be as follows: =IF(d4<40,3*d4-5.5,d4)

The equation could then be copied to cells E5 to E15

Practice 4

With the data below, do the following: If the date is greater than 1/1/91 (January 1, 1991), let the New Date equal the Old Date minus 100 days; otherwise, put in the original date.

Yes

The equation in cell E4 would be as follows: =IF(b4>date(1991,1,1),b4-100,b4)

The equation could then be copied to cells E5 to E15

In cell F4, the equation would be: =E4

Then in cell F$, click on Format, Cell, Number tab (if not already there), Date and pick the format of the date that you want to be displayed. Copy the equations in E4 and F4 to E5 through E15 and F5 through F15, respectively.

Practice 5

With the data below, do the following: If the first letter of Name is “W”, replace it with a “V” in column E; otherwise, leave the name the same

This is a tough one using text functions. LEFT() selects digits starting from the left: RIGHT() selects digits starting from the right; and LEN() counts the number of digits (including spaces) in the text.

Yes

The equation in cell E4 would be as follows:

=IF(LEFT(c4,1)=”W”,”V”&RIGHT(C4,LEN(C4)-1),C4)

The equation could then be copied to cells E5 to E15

Note that the quotations (“ “) must be used to identify it as text. LEFT(c4,1) says to look at the digit in c4 that is first on the left-hand side. RIGHT(C4, LEN(C4)-1,C4) is more complex. First, the LEN(c4) identifies the total number of digits (including spaces) in the name. The RIGHT() indicates to include the digits in cell C4, beginning at the right, for a total of all of the digits (that’s the LEN() function) except for the first digit (that’s why we subtract 1 from the total identified by the LEN() function). Otherwise, put in the original name, C4.

Practice 6

With the data below, do the following: If the value is greater than $60, then put 60; if it is between $40 and $60, put 45; otherwise, the original value. Use NESTED IF statements.

This uses Nested IF statements; i.e., IF statements inside of other IF statements. There are different ways to do this, but the most logical is to start with the highest number and work your way down.

Yes

The equation in cell E4 would be as follows: =if(d4>60,60,if(d4>40,45,d5))

And copy the equation. Note that there are two IF equations and we need two “end parentheses” [or ))] in order to close both of them.

Practice 7

With the data below, do the following: If the value is between $30 and $60, then replace it with 45; otherwise, the original value. Use IF(AND) statements.

Yes

for both

The equation in cell E4 would be: =IF(AND(d4>30,d4<60),45,d4)

The AND() function means that both conditions must be satisfied for it to be true.

Practice 8

With the data below, do the following: If the value is outside $30 and $70 (i.e., less than $30 or greater than $70) , then keep the original value; otherwise, replace it with $50. Use IF(OR) statements.

Yes

For either

The equation for cell E4 would be: =IF(OR(D4<30,D4>70),D4,50)

The OR() function means that either condition can be satisfied for it to be true.