Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need formula (2002 SP-2)

    Thanks for your help, Hans.
    Clark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •