Forum Discussion

kieranmac's avatar
kieranmac
Copper Contributor
Jan 13, 2024

if statement seems reversed

I am using if statements and it seems to be outputting the reverse of what is correct. 

 

eg:

 

=if(10>60,1,0) gives me a true value of 1

 

it seems like it is always showing true no matter what the value is

another example is shown below;

 

 

any help would be appreciated

  • kieranmac Long story short. G2 contains a text based on what I guess a formula like =LEFT(E2,2).

    A text is always considered greater than a number. That's why you will always get 1.

     

    You have two options.

    1) change the formula in G2 to =LEFT(E2,2)*1 or =--LEFT(E2,2). Both these will turn the result into the number 10 and your IF formula will work.

    2) change the IF formula to =IF(C4>"60",1,0), in case you really want to compare texts.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    kieranmac Long story short. G2 contains a text based on what I guess a formula like =LEFT(E2,2).

    A text is always considered greater than a number. That's why you will always get 1.

     

    You have two options.

    1) change the formula in G2 to =LEFT(E2,2)*1 or =--LEFT(E2,2). Both these will turn the result into the number 10 and your IF formula will work.

    2) change the IF formula to =IF(C4>"60",1,0), in case you really want to compare texts.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    kieranmac 

    The formula =IF(10>60, 1, 0) is working correctly based on the condition you provided. If the formula =IF(10>60, 1, 0) is not giving you the expected result of 0, there could be a few reasons for this behavior. Here are some things to check:

    1. Cell Format: Ensure that the cell containing the formula is formatted as a general or number format. If the cell is formatted as text, it might interpret the result differently.
    2. Data Types: Confirm that the values being compared (10 and 60) are treated as numbers. Sometimes, if they are stored as text, comparisons may not work as expected.
    3. Formula Errors: Check for any additional characters or errors in the formula. Retype the formula to ensure it's entered correctly.
    4. Worksheet Recalculation: Ensure that the worksheet is set to recalculate formulas automatically. You can go to the "Formulas" tab, click on "Calculation Options," and choose "Automatic."
    5. Cell Dependencies: Check if there are any other formulas or cells that depend on the cell with this formula. If there are circular references or conflicting formulas, it might affect the result.
    6. Excel Version: In rare cases, there might be issues related to the specific version of Excel. Make sure your Excel software is up to date.

    AI was partially deployed to support the text.

    If after checking these factors you're still experiencing issues, please provide more details about the context in which this formula is used, and I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc.

    In this link you will find some more information about it:

    Welcome to your Excel discussion space!

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources