BlosMusic
asked on
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
EE-sample.xlsx
Last Comment
Joe Howard
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.
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?
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?
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?
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?
ASKER
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
EE-sample.xlsx
ASKER
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!!
The formula in D5 should be like this.....
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.
=IF(AND(B4="Ferrari",B5="Coloured",OR(B6=832,B6="902 S2")),1,99)
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.
ASKER
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!
Thanks all!
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Please mark your question as Solved by selecting an Acceptable Solution.
Thanks.
Thanks.
ASKER
So quick, and very helpful . . . .
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
