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

    Proper use of IF and OR (Excel xp)

    I'm trying to put together a formula that will evaluate E381. If it is less than 30 i want to make E378 a 2. If it is less than 50 i want to make E378 a --. The problem is that E381 can have an * or ** at the end of it.

    So if E381 is 40*, i want e378 to be a --. If E381 is a 29** i want e378 to be a 2. I put together a formula but it's not seeing 40* as a number. I've tried (value(E381)) thinking that E381 would be recognized as a number but i get an error.

    What is the proper order to do this formula. Thank you

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

    Re: Proper use of IF and OR (Excel xp)

    =SUBSTITUTE(E381,"*","")

    will return a string that will be evaluated as a number for further calcs. What do you want to do if the value of E381 is >=50?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper use of IF and OR (Excel xp)

    The following formula should do exactly what you asked for.

    =IF(VALUE(REPLACE($E$381,3,2," "))<30,"2",IF(VALUE(REPLACE($E$381,3,2," "))<50,"--","Not sure"))

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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