Results 1 to 9 of 9
Thread: Need formula (2002 SP2)

20050103, 14:04 #1
 Join Date
 Mar 2001
 Posts
 166
 Thanks
 0
 Thanked 0 Times in 0 Posts
Need formula (2002 SP2)
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=""),"",E1C2+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?

20050103, 14:39 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Need formula (2002 SP2)
<P ID="edit" class=small>(Edited by sdckapr on 03Jan05 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=""),"",H1F2+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

20050103, 14:41 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Need formula (2002 SP2)
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>

20050103, 16:16 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need formula (2002 SP2)
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>
Legare Coleman

20050103, 22:06 #5
 Join Date
 Mar 2001
 Posts
 166
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need formula (2002 SP2)
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

20050103, 22:20 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Need formula (2002 SP2)
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.

20050104, 13:27 #7
 Join Date
 Mar 2001
 Posts
 166
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need formula (2002 SP2)
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

20050104, 13:43 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Need formula (2002 SP2)
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.

20050104, 18:32 #9
 Join Date
 Mar 2001
 Posts
 166
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Need formula (2002 SP2)
Thanks for your help, Hans.
Clark