The Anatomy of an Excel IF Function,
Part 4: Nesting Another Function Within an IF Statement
(page 2 of 3)
Now we need to enter a formula in cell E2 that will calculate a running balance. Remember, we want E2 to remain blank if an Actually Expense has not been enter in D2. Let's use our trusty pseudocode to give us some direction:
IF cell D2 is blank, then leave E2 blank, otherwise subtract D2 from the deposit.
Well, that's too easy. Let's write the formula and see if it works. Enter the following formula in E2:
=IF(D2="", "", deposit-D2)
As you can see, we are using the name deposit to refer to cell G2. Also notice that we do not enclose the name of cells or ranges in double quotations like we did with text string values.
The above formula calculates the the correct value in E2. However, what happens when we copy this formula to the cells below in the Balance column?
The copied formulas in the Balance column below E2 subtract only the newest expense that is entered in the Actual Expenses column from the deposit of $100,000. We need to subtract all of the expenses in the Actual Expense column up to that point. For example, the copied formula in E9 needs to subtract the expense in D9 summed with all the expenses up to that point, which would be the sum of the range D2:D9.
Let's consider our formula in cell E2 again and understand how it works:
=IF(D2="", "", deposit-D2)
This formula will subtract the deposit, $100,000, from the expense in D2, $4,060.49, and give us the correct result, $95,939.51. This is the correct result. So far so good!
However, let's see what happens in cell E3. The copied formula in E3 will subtract the deposit, $100,000, from the expense in D3, $4,761.38, and give us the result of $95,238.62. This is incorrect because we need the formula to not only subtract D3 from the deposit, but all the expenses up to that point which would be the sum of the range D2:D3.
page 1 | 2 | 3 | next page >>