IF THEN AND Statement HelpSolved/Closed

Question

Hello Everyone,

I am trying to make a formula work and I'm having a headache from it. 

Here it is:

=IF(AND($B$2<"5.9", $B$9="1.012"),$B$2-"0.1")

Very basic, or so I thought, but it does not give the right answer. 

What I am trying to do is on Cell $F$19 I want to check IF B2 is 5.9 or less AND B9 is 1.012 THEN B2 minus 0.1

Any suggestion is greatly appreciated.

Thanks

Ralph Lemus

TheParoxysm · Answer

=IF(AND(test1,test2,test3,...),true result, false result) Example: If A1>B1 and C1B1,IF(C1B1,C1

ralphl · Answer

So basically us it like so:

=IF(AND(B2<"5.9",B9="1.012"),B2-"0.1",)

Thanks

TheParoxysm · Answer

Yep.  Also, and I just realized this, take your numbers out of quotation marks. 

=IF(AND(B2<5.9, B9=1.012), B2-0.1) 

Hopefully this helps!

ralphl · Answer

For some reason I'm getting a "FALSE" in the cell. Here is my formula again:

=IF(AND(B2<5.9,B9=1.012),B2-0.1)


Thanks

ralphl · Answer

The funny part is that it's right. I should get an answer and it's giving a false answer. I have 5.8 in B2 I have 1.012 in B9 so essentially i should get 5.8 as an answer. But it's not.
Help

aquarelle · Answer

I have 5.8 in B2 I have 1.012 in B9
With your formula, the result is 5.7 not 5.8

ralphl · Answer

Im sorry I have 5.9 in B2. The answer should be 5.8.

aquarelle · Answer

Write your formula like this :
=IF(AND(B2<=5.9,B9=1.012),B2-0.1,false result)

ralphl · Answer

For some reason it does not take. Talk about a headache! 

All the Cells have the correct info and not dice!

aquarelle · Answer

Hi,
Could you tell me what is happened, what sort of result you had ?
Please load a brief example of your excel file (without any private or personal data) on https://authentification.site then copy past the created link to your next answer.
See you

ralphl · Answer

Ok, here it is

https://authentification.site/914280127.html

The thing is that it does not give the right answer. It gives a blank answer/nothing in the cell. 

Thanks so much.

aquarelle · Answer

Arff, I have Office 2003, so could you save it to the Excel 2003 format, load it again and copy/past the new link, please.

ralphl · Answer

Here you go...
https://authentification.site/894439785.html

Thanks again

aquarelle · Answer

Ok, what I have seen is that your cells format is in Text format then you have to put it in Number format otherwise Excel can't calculate the formula.
See you

ralphl · Answer

Ok, So went ahead and changed that cell to number and I still don't get a correct answer.
Any other thought?

aquarelle · Answer

You have to change the format for all cells. Did you?

ralphl · Answer

All of them are in number

aquarelle · Answer

Is there an error written ? What is happen ? I don't understand because it works for me.

ralphl · Answer

It does not give the number that need. The answer should be 5.8, since i have it like follows, it does not show anything:

=IF(AND(B2<5.9, B9=1.012), B2-0.1,"")

aquarelle · Answer

Normal that it doesn't work because as I said to the message NÂ°<11>, you have to write =IF(AND(B2<=5.9, B9=1.012), B2-0.1,""), cause "greater than or equal "sign is "<=" in Excel. Try it.

ralphl · Answer

Actually I had the = sign in there before and it did not work, BUT I copied and pasted it and it worked. 

Thank you so much aquarelle. You have saved me from a heart attack! 

Thanks again!

aquarelle · Answer

Wonderful !!! Very happy and it was a pleasure to help you. Maybe see you in the future somewhere on kioskea !
Bye

aquarelle · Answer

Yes, it is possible. I'm going to sleep now because in France where I am, it is 00:48 AM, but tomorrow, if you want, we could look at it.

ralphl · Answer

Au revoir, and Merci beaucoup

aquarelle · Answer

Hello,
Sorry, but finally, I couldn't do it yesterday. Try this formula and tell me back if it is all right : 
=IF(OR(B9=1.012,B9=1.013,B9=1.014,AND(B2<=5.9)),B2-0.1,IF(OR(B9=1.011,B9=1.01,B9=1.009,AND(B2<=5.9)),B2-0.2,IF(OR(B9=1.008,B9=1.007,B9=1.006,AND(B2<=5.9)),B2-0.3,IF(OR(B9=1.005,B9=1.004,B9=1.003,AND(B2<=5.9)),B2-0.4,IF(OR(B9=1.002,B9=1.001,B9=1,AND(B2<=5.9)),B2-0.5,0)))))
See you

ralphl · Answer

Listen, there is no reason for you to say sorry. I really appreciate all that you have done. You have been a saint. I should be the one saying sorry for the inconvenience. 

OK, back to the formula, it seems right but it does not adjust the numbers correctly. no matter what I put in B9 cell it gives me the answer of 5.8. I have been looking through the formula, and theoretically it should have worked, but it didn't. 

Once again thanks for your efforts and time

aquarelle · Answer

Hello,
No, I'm not a saint O:-), but someone who like to help as possible and I don't like to say something and not to do it ;)
So don't think you bother me ! Ok ?
Effectively, the formula doesn't work, try this new one : 
=IF(AND(AND(OR(B9=1.012,B9=1.013,B9=1.014),B2<=5.9)),B2-0.1,IF(AND(AND(OR(B9=1.011,B9=1.01,B9=1.009),B2<=5.9)),B2-0.2,IF(AND(AND(OR(B9=1.008,B9=1.007,B9=1.006),B2<=5.9)),B2-0.3,IF(AND(AND(OR(B9=1.005,B9=1.004,B9=1.003),B2<=5.9)),B2-0.4,IF(AND(AND(OR(B9=1.002,B9=1.001,B9=1),B2<=5.9)),B2-0.5,0))))) 
See you

IF THEN AND Statement Help

27 responses

Similar discussions

Subscribe To Our Newsletter!