AND nested with OR gives wrong answer

I have many problems at the moment with IF statements. Why is it that sometimes (see attached sample) I just get the text I entered in the formula box and not the formula working as it should? That's my first question. There will be others if I don't give up beforehand with Excel's weirdness. See attached example.
EE-sample.xlsx
BlosMusicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
If the cell format is set to Text, it will show the formula instead of the value. To change this change the cell format to General.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Click on B6 on Sheet1, change the format from Text To General (Home Tab --> Number -->From available formats select General).
Now while B6 is still selected, press F2 (Function Key) and press Enter.
The B6 will show the value returned by the formula, not the formula itself.

Does this help?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The formula in D5 is returning the correct output as per the formula in the cell.
What are you expecting it to return?

As per the current formula, if B4 is Ferrari and B5 is Coloured and B6 is either 832 or 902 S2, the formula will return 1, else it would return 99.
Are you expecting something else?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

BlosMusicAuthor Commented:
This doesn't do anything. Please look at my sample, that I have amended, below, and . . . . well, can you see what is wrong? Beats me . . . .
EE-sample.xlsx
0
BlosMusicAuthor Commented:
Sorry, getting ahead of myself here! I will look at sktneer's suggestion in a moment, but in the meantime (and this is where I was heading), I just get the wrong answer from the formula. I appreciate that this is to with formatting (I suppose), but boy is it frustrating!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The formula in D5 should be like this.....

=IF(AND(B4="Ferrari",B5="Coloured",OR(B6=832,B6="902 S2")),1,99)

Open in new window


832 is number not text. In your previous formula, you enclosed 832 with double quotes like "832", therefore the formula assumes B6 should be a text 832 not the number 832. And Text 832 and the number 832 are not equal.
0
BlosMusicAuthor Commented:
You did it! Yeaaaah! That's working. Doh! Sorry if I seem thick. Excel isn't necessarily humanly intuitive. Brilliant it may be, but to a poor old sod like me it's weird on occasions!
Thanks all!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Don't worry. It happens with all of us sometimes that we miss some basic things and panic. :)
Just a human error nothing else.

Happy to help. :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please mark your question as Solved by selecting an Acceptable Solution.

Thanks.
0
BlosMusicAuthor Commented:
So quick, and very helpful . . . .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.