Excel formula not work?

Discussion in 'OT Technology' started by dissonance, Nov 14, 2007.

  1. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    So here is a screen shot of the worksheet:
    [​IMG]
    The formula you see in the formula bar is for cell R50, which is displaying 2.

    It makes no sense, Excel has been doing this to me all day, it doesn't matter what the value in the targeted cell is, the IF statement just spits out one of the possible values randomly.

    =====================
    NEVERMIND, FIGURED IT OUT
    =====================
     
    Last edited: Nov 14, 2007
  2. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    so why not delete the thread or share with us what was wrong? someone else might be having the same issue.
     
  3. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    sorry.


    The cell it was using to determine whether the logic statement was true or false was a formula itself (R49). I set it to be formatted as a number value but when I referenced it in the R50 formula, it thought it was a text value (and Excel views text values as greater than 1). So I was able to fix the problem by using this in cell R50:
    =IF(R49+0>1,"2","1")​
    By doing that, it forced it to recognize R49 as a numeric value instead of letting it decide on its own.
     
  4. cxhatchback

    cxhatchback New Member

    Joined:
    Apr 30, 2006
    Messages:
    22
    Likes Received:
    0
    Instead of using R49+0, change the then and else parameters to 2,1 without the quotes. The quotes are what is causing excel to read it as text, you don't need them.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Wow - you bumped a thread from 2 weeks ago to give a shitty answer?

    :ugh:
     
  6. cxhatchback

    cxhatchback New Member

    Joined:
    Apr 30, 2006
    Messages:
    22
    Likes Received:
    0
    The "shitty answer" is the reason he probably has problems with a lot of his formulas. My post was mainly in response to the second post, to help others who might be having the same issue. But thanks for being a dick.
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Fuck dude - your answer doesn't fix the fucking problem! It's reading the source cell as text - THAT'S what caused the problem. And the fact that you bumped a 2 week old thread to provide incorrect information makes it even worse!
     
  8. dissonance

    dissonance reset OT Supporter

    Joined:
    May 23, 2006
    Messages:
    5,652
    Likes Received:
    1
    Location:
    KS
    I had tried that and it didn't matter. And my answer was, and as far as I know, the only thing that works.

    and yea... as 5Gen_Prelude said:
    And so you know, I usually don't have any problems that take me more than a couple of minutes just looking at the formulat to fix.... this kind of shit only starts when Excel gets pushed to well over a MB in size.

    anyway, i think we can let this thread die off now
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You could use if(value(R49))>1,2,1) - it's a bit more refined when Excel doesn't force it to be numeric, but your solution works fine.
     

Share This Page