Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Long formula needs help (excel xp)

    I have 2 cells S70 and V70. The possible values are numerals or a pound sign (#). I have zeros turned off so if S70-V70=0, i need it to put in a zero. Also, if s70 or v70 has a pound # sign i need it to put in a double hyphen --.

    =IF(OR(V70="#",S70="#"),"--",S70-V70)

    =IF(S70-V70=0,"0",S70-V70)

    How do i put these 2 formulas together? is it possible. thank you

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Long formula needs help (excel xp)

    <pre>=IF(OR(V70="#",S70="#"),"--",IF(S70-V70=0,"0",S70-V70))
    </pre>

    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Long formula needs help (excel xp)

    Sure. The best way to build and test long formulas is build the pieces and then cut and paste the components into the "monster" formua.

    If you want text "0" only when S70-V70=0 (which will return negative values for S70-V70):

    =IF(OR(V70="#",S70="#"),"--",IF(S70-V70,"0",S70-V70))

    If you want text "0" when S70-V70<=0:

    =IF(OR(V70="#",S70="#"),"--",IF((S70-V70)<=0,"0",S70-V70))

    If you want a zero instead of text "0" when S70-V70=0 (which will return negative values for S70-V70):

    =IF(OR(V70="#",S70="#"),"--",S70-V70)

    If you want a zero instead of text "0" when S70-V70<=0:

    =IF(OR(V70="#",S70="#"),"--",MAX(0,S70-V70)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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