IF behaving ... well IFfy

[brynbaker]

I have a colleague with an IF formula that does the opposite of what's expected

=IF(False,"X","Y")

returns X every time! Of course the false response should be Y in this case. Even in afresh workbook.

I've copied part of her spreadsheet onto my machine and of course everything behaves as expected.
Restarted Excel, no better. [I've just suggested a reboot and she can't do it yet.]

[Norie]

What is False?

If it was a boolean value I would have expected to see FALSE.

[amy_d2]

Hi

have you tried like this

=IF(False,"Y","X")

[Richard Buttrey]

Hi,

Time to ask your colleague if she would upload her workbook here.

When you say you've copied part of her spreadsheet did you also copy everything on which her formula depended as well? What do you see if you open her workbook on your PC?

[brynbaker]

Actually it would be FALSE, but if you type false into excel it makes it upper case for you. I was posting from back at my desk and imprecise.
We had little time to experiment but isolated the formula in a new workbook. The original was a simple one cell dependency.

This is one of those daft things; it seems too fundamental to be a simple bug, so more likely a user error compounded by my failing to see it. I usually get rapid brain fade when dealing with false - somehow true is more reliable As it wass friday everyone left early so further investigation will have to be Tuesday (Note for non England readers, Monday is a public holiday).

[brynbaker]

Update: Colleague is on holiday this week. I'll report following her return.

[brynbaker]

My colleague finally reports it's OK now. It must be one of those transient errors that we see sometimes. I knew that so fundamental a fault could not be common (was indeed hadly believeble) but I saw it with my own eye. When using evaluate you can resolve to something like this

Please Login or Register  to view this content.

which returns TRUE correctly but a little oddly. Perhaps that's what I saw?

Thanks nory, amy, Richard and any others who pondered this post.
I consider it closed

[Fotis1991]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED",as per our Forum Rule #9. I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[brynbaker]

Thanks for the advice; I had considered both actions but: it is not solved exactly, so no-one helped, I always give recognition when appropriate.
I understood that marking it solved would stop people looking for a solution but I'd still be interested if anyone has similar experience or a real answer.

[Richard Buttrey]

Thanks for the advice; I had considered both actions but: it is not solved exactly, so no-one helped, I always give recognition when appropriate.
I understood that marking it solved would stop people looking for a solution but I'd still be interested if anyone has similar experience or a real answer.

I suspect Fotis flagged it up since your last comment concluded with "I consider it closed", which is I would suggest rather conclusive, and one of the clearest statements I've seen on these pages where clarity is not always the order of the day.

[Fotis1991]

Correct Richard. Thank you!

Ok my first lanquage is not the English but i think that when someone says: ""I consider it closed", is much more than clear.

@ brynbaker

As you see i unmarked this from "Solved". We'll be waiting for someone to offer you a "real answer"

Of course i am not able to understand what this mean? All the suggestions that you got are not real answers?

[Richard Buttrey]

@brynbaker

....so can you clarify your last comment when you said in post #9 'it is not solved exactly'. What is it that is not solved?

As for the =IF(FALSE,FALSE,TRUE) giving TRUE, whilst it may seem a little opaque it does sort of make sense to me.

The first IF element implicitly says, if (the element FALSE) is false then return false else true. This first element is not false since FALSE IS false, hence it's TRUE. ....or at least that's the way I'm rationalising it.

[Pete_UK]

In the general form we have:

=IF(condition, action_if_TRUE, action_if_FALSE)

and condition will be evaluated as either TRUE or FALSE.

In your case you do not have a condition to evaluate, but you do have a result (FALSE), thus the action_if_FALSE is returned, which happens to be TRUE in your case.

Hope this helps.

Pete