6

I have two columns of data and I wanted to identify the swap candidate based on two criteria:

  1. If first column < 0.5 OR
  2. If first column divided by second column < 10.

And here's my formula =IF(OR(E2<0.5,(E2/F2)<=10),"Swap Candidate"," ")

(The first and second column are in column E and F in the workbook). But the function doesn't work as it should. It instead returns all values as true while it's not. For example in the highlighted cell.

enter image description here

I also ran the formula evaluate for the highlighted cell. And it shows FALSE after evaluating the OR statement but then return the value if true. enter image description here

The numbers are stored in the correct format. So I don't get what's going on with it. Could anyone help me out please?

Improve this question
8
  • 1
    Are automatic calculations on in your workbook? Commented Apr 22, 2015 at 14:26
  • 1
    It works correctly for me. This sounds dumb, but have you tried closing excel and reopening the workbook? Commented Apr 22, 2015 at 14:28
  • This is a long shot (and really silly), but, you don't have the number formatting for the formula cell set to @"Swap Candidate" do you? Commented Apr 22, 2015 at 14:30
  • Thanks for all your help. I just tried reopen it, and checked my calculation in on automatic. Still doesn't work. And I didn't changed the number formatting in the @"Swap Candidate" column, it's under General. I guess it must be something wrong with my computer... Commented Apr 22, 2015 at 14:38
  • Do you have any conditional formatting? Commented Apr 22, 2015 at 15:15

2 Answers 2

Reset to default
1

Check if your workbook are calculating, press F9 to force you workbook to calculate

Edit 1:

I tried input your formula in my Excel(PT-BR) and works, look:

enter image description here

Edit 2:

See if your workbook aren't calculating, click in "Formulas" and check if "Calculate Options" are "Manual" or "Automatic Except for Daata Tables", if one of options are checked, change to "Automatic"

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Old question but this was so annoying figured I'd post what worked for me...

My OR statement was structured as follows:

=OR(FIND("**",[@Status])=1,[@SortOrder]=1,[@Status]="Active",[@Status]="Focus Mode")

The error was occurring when the OR function evaluated the FIND() function first - if "**" was not present in the "Status" column, the function returned a #VALUE! error instead of proceeding to evaluate the rest of the OR statement. (This is why, when I put =[@Status]="Focus Mode" in a separate column, it evaluated correctly as TRUE, but after embedding it in the OR statement, it returned #VALUE!)

To fix this error I adjusted the formula as follows:

=OR(IFERROR(FIND("**",[@Status])=1,FALSE),[@SortOrder]=1,[@Status]="Active",[@Status]="Focus Mode")
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.