# Thread: Need formula (2002 SP-2)

1. ## Need formula (2002 SP-2)

Someone on this forum was kind enough to help me set up a worksheet with ledger accounts, where col A has the date of a posting, col B describes the posting, col C the debit, col D the credit, and col E the balance. In a liability account an entry in col C decreases the running balance, e.g., the formula in col E is =IF(AND(C2="",D2=""),"",E1-C2+D2). I want to insert three columns after col B (which moves everything to the right, so that col H now contains the running balance). Cols A and B stay the same, cols C, D, and E will now contain new data that will affect the balance. Here's the question: I need to write two new formulas, one to be placed in col F that multiplies the value in col C by .05. The value in F then affects the value in H. Secondly, I need a formula for col G that adds the values in cols D and E and multiplies the sum by .05. The value in G then affects the value in H. I've been able to come up with the formula for F: =IF(AND(C2=""),"",C2*0.05). And I have tried to make up a formula for G: =IF(AND(D2="",E2=""),"",E2+D2*0.05). But the result of this latter formula is the #VALUE! error in H. What am I doing wrong?

2. ## Re: Need formula (2002 SP-2)

<P ID="edit" class=small>(Edited by sdckapr on 03-Jan-05 10:39. Fixed formula in G to multiply by the quantity and not just D2)</P>Is your formula in H2:
<pre>=IF(AND(F2="",G2=""),"",H1-F2+G2)</pre>

which is what you would get if you inserted the 3 cols or is it something else.

If that you would get a #value error when C was blank but either D and E were not blank or if C was not blank but D and E were blank.

What do you want the result to be when this is the case?

Perhaps putting in F2:
<pre>=IF(AND(C2="",D2="",E2=""),"",C2*0.05)</pre>

and in G2:
<pre>=IF(AND(C2="",D2="",E2=""),"",(E2+D2)*0.05) </pre>

would solve the problem by putting in zeroes when the values are blanks. It would only display "nulls" when all 3 are blank.

If you want to hide the zeroes, you can turn off the display of zeroes in the workbook under tools- options - View(tab)- uncheck "zero values"
or you could use a custom format to not display zeroes in these cells

Steve

3. ## Re: Need formula (2002 SP-2)

The problem is that "" is a text value; you cannot use that in an addition/subtraction. Moreover, if you want to add E2 and D2 and multiply the sum by 0.05, you must put parentheses around E2+D2 , otherwise only D2 will be multiplied by 0.05. So try

<code>=IF(AND(D2="",E2=""),0,(E2+D2)*0.05)</code>

Note: in the other formula, you don't need AND since you only check one cell:

<code>=IF(C2="",0,C2*0.05)</code>

4. ## Re: Need formula (2002 SP-2)

I think you probably want to use the OR function instead of the AND function, and you need an aditional set of parenthesis if I read your question correctly:

<pre>=IF(OR(D2="",E2=""),"",(E2+D2)*0.05).
</pre>

5. ## Re: Need formula (2002 SP-2)

Hans' two formulas work, but as I fill the formulas in down the rows, the current balance appears in every cell. Is there a way to hide this and not have the balance appear unless there are values in its row?
Clark

6. ## Re: Need formula (2002 SP-2)

You can't make the formula return a blank if the row is blank - that would restart the running sum. I think you will have to use conditional formatting:
- Select the cells in column H with the formula (starting in H2).
- H2 should now be the active cell within the selected range.
- Select Format | Conditional Formatting...
- Select Formula Is from the first dropdown list.
- Enter the following formula in the box next to it:

=(H2=H1)

- Click the Format... button
- Set the font color to the same as the background color (probably white)
- Click OK twice.
- Excel will automatically adjust the conditional formatting formula for the other cells.

7. ## Re: Need formula (2002 SP-2)

This works. Thanks, Hans. I had a macro for inserting a row in this sheet. It went like this:

Sub InsertRowSalesTax()
Dim lRow As Long
lRow = Selection.Row - 1
With ActiveSheet
Selection.EntireRow.Insert
Range("E1").Offset(lRow - 1, 0).AutoFill _
Destination:=Range(.Range("E1").Offset(lRow - 1, 0), .Range("E1").Offset(lRow + 1, 0)), _
Type:=xlFillDefault
End With

End Sub

How should I alter this to work with the new sheet (with columns A to H, and formulas in F, G, and H)? And can you help me with a macro to delete a row?
Clark

8. ## Re: Need formula (2002 SP-2)

Try
<code>
Range("F" & lRow & ":H" & lRow).AutoFill _
Destination:=Range("F" & lRow & ":H" & (lRow + 2)), _
Type:=xlFillDefault
</code>
You don't need With ActiveSheet and End With here.

To delete a row, use .EntireRow.Delete instead of .EntireRow.Insert. You may need to fill down from the row above the deleted row to restore the formulas.