Tips for Spreadsheets

*Summary: Perform condition-based tasks depending on cell values in a Microsoft Excel 2003 spreadsheet.*

What if you need to create a Microsoft Excel 2003 spreadsheet where a particular cell's value should be one of two values based on another cell's value?

For example, let's create a rudimentary spreadsheet detailing a company's profits or losses for a particular quarter:

A1: April

A2: May

A3: June

B1: $20,506

B2: -$11,202

B3: $8,506

The value of B4 should be the sum of B1 through B3:

B4: =SUM(B1:B3)

Right now B4 would show a value of $17,810.

Now we want the spreadsheet to say that there was a net profit this quarter if the value of B4 is positive. This way, if the numbers change (perhaps an audit showed a loss in June?) this text wouldn't have to be removed. To do this, we can use the =IF function in cell B6.

B6: =IF(A4>0,"There was a net profit this quarter")

The '>' is called an operator, and means greater than. This function simply states that IF the value of A4 IS GREATER THAN 0, the value of B6 should be "There was a net profit this quarter".

**Side Note: Operators**

For reference, here are the following operators you can place between cells and values.

**=** Equals

**>** Greater Than

**<** Less Than

**>=** Greater Than or Equals

**<=** Less Than or Equals

**<>** Does not Equal

**Now back to the discussion.**

If B3 is changed to a value of:

B3: -$29,456

The value of B6 would be FALSE, since the =IF condition doesn't pass (the value of A4 is not greater than 0).

We can add to the =IF in cell B6 by handling this FALSE condition.

B6: =IF(A4>0, "There was a net profit this quarter", "There was a net loss this quarter").

Now the value of B6 should be "There was a net loss this quarter", since the value of A4 was not GREATER THAN 0.

**Side Note: Empty False Condition**

What if we wanted the value of B6 to be blank and not FALSE if A4 was not greater than 0? Feed the false condition an empty string:

B6: =IF(A4>0, "There was a net profit this quarter", "").

**Nesting =IF functions**

Sounds good, right? However, what if there was no net profit NOR net loss? We can simulate this by the following:

B3: -$9,304

Now, the value of B4 would be 0, but since 0 is not greater than 0, the value of B6 is "There was a net loss this quarter". To handle this case, we can nest two =IF functions together. How should this work? Let's think about it logically.

(IF B4>0): First, check to see if B4 is greater than 0. If so, show there was a profit.

(B4 IS NOT >0, IF B4 < 0): If that is not the case, then check to see if B4 is less than 0. If so, show there was a loss.

(B4 IS NOT > 0, B4 IS NOT < 0): If neither of the above are the case, the company must have broken even this quarter. B4 must be 0 exactly.

Here is the B6 value that handles this complicated condition. We will nest the =IF statements with parenthesis.

B6: =IF(B4>0, "There was a net profit this quarter", IF(B4<0, "There was a net loss this quarter", "The company broke even this quarter"))

Note that the nested IF statement does not need an equals sign. This is because only one equals sign is needed at the beginning of a function to differentiate a function from a cell value.

According to the Microsoft Excel 2003 documentation, you can nest up to 7 =IF statements.

**Calculation based on IF**

What if we wanted a calculation done based on the value of the =IF statement? For example, let's return to the original values per quarter:

B1: $20,506

B2: -$11,202

B3: $8,506

What if we want the spreadsheet to pay taxes based on the profit? For this discussion, let's assume a VERY simplified tax system based on profits:

0: $0

1-$15,000: 10%

$15,101+: 10% on the first $15,000, 12% thereafter

To create this, let's consider this logically:

(IF B4>0): Check to see if B4 >0. We must have a profit to pay taxes in this theoretical system.

(IF B4<15001): Assuming the above is true, check to see if B4 is less than 15,001

(B4 * 0.1): Assuming that B4 is greater than 0 and B4 is less than 15,001 we want the tax to be 10% of the profit.

((B4-15000)*0.12) + (15000*0.1): Now things get complicated. If B4 is greater than 0 and is not less than 15,001, we have to do two calculations. First, get 12 percent of all profits collected above 15,000, or B4 - 15000. Next, get 10% of all profits 15,000 and less, or 15000 * 0.1. Yes, we could simplify things since we know 15,000 * 0.1 is 1500, but by keeping in the 15,000 value, we could change the number to, say, 16,000 real easy if the tax system changes next year.

(0): Back to the first IF statement. If B4 is not greater than 0, the tax should be 0.

Now for the formula for cell A7. We must be sure to match all parenthesis - if the formula has five opening parenthesis, it must have 5 closing parenthesis to match.

B7: =IF(B4>0, IF(B4<15001, B4*0.1, ((B4-15000)*0.12) + (15000*0.1)) ,0)

Since the value of B6 is "17810", the value of B7 would be "1837.2".

**Spacing Matters**

Microsoft Excel 2003 is picky about spacing in some cases and not in others. There cannot be a space between the function name and a parenthesis. However, you can separate the conditions with spaces after the commas.

Add: Del.icio.us | Digg | Furl | My Yahoo!

*Last Modified on: July 13, 2008, at 12:04 A.M. EDT*

Return to the Microsoft Excel 2003 Functions and Formulas page.

Search the Site: