SOLVED

if statement seems reversed

Copper Contributor

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;

 

image.png

 

any help would be appreciated

3 Replies

@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.

best response confirmed by HansVogelaar (MVP)
Solution

@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 

Thank you so much! I thought I was going crazy. Multiplying by 1 was a quick fix. 

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

@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.

View solution in original post