IF THEN AND Statement Help

Solved/Closed
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008
- Dec 9, 2008 at 01:21 PM
 ralphl - Dec 15, 2008 at 08:44 AM
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

27 replies

aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 9, 2008 at 03:41 PM
I have 5.8 in B2 I have 1.012 in B9
With your formula, the result is 5.7 not 5.8
1
TheParoxysm
Posts
169
Registration date
Sunday December 7, 2008
Status
Member
Last seen
March 25, 2011
74
Dec 9, 2008 at 03:46 PM
You've very good with Excel.
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490 > TheParoxysm
Posts
169
Registration date
Sunday December 7, 2008
Status
Member
Last seen
March 25, 2011

Dec 9, 2008 at 04:03 PM
Yes, I know some basic things about it but not all cause there are so many functions.
I use it every day for my work and when I need new functions to facilitate my work, I search in Excel help file or to Internet.
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 10, 2008 at 05:23 PM
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.
1
TheParoxysm
Posts
169
Registration date
Sunday December 7, 2008
Status
Member
Last seen
March 25, 2011
74
Dec 9, 2008 at 01:39 PM
=IF(AND(test1,test2,test3,...),true result, false result)

Example: If A1>B1 and C1<D1 then the result is 5, otherwise it's 0. As a nested IF statement:

=IF(A1>B1,IF(C1<D1,5,0),0)

Using AND, it's a little neater:

=IF(AND(A1>B1,C1<D1),5,0)

Remove the absolute cell reference and try it. Some people overuse absolute cell references, and cause themselves a headache when it doesn't work the way they THOUGHT it worked when they changed the table.
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 9, 2008 at 01:55 PM
So basically us it like so:

=IF(AND(B2<"5.9",B9="1.012"),B2-"0.1",)

Thanks
0

Didn't find the answer you are looking for?

Ask a question
TheParoxysm
Posts
169
Registration date
Sunday December 7, 2008
Status
Member
Last seen
March 25, 2011
74
Dec 9, 2008 at 01:58 PM
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!
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 9, 2008 at 02:38 PM
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
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 9, 2008 at 02:54 PM
Hi,
It is normal that you have this error because you forgot to indicate what will happen (then the formula to calculate) if this condition is not respected.
=IF(AND(B2<5.9,B9=1.012),B2-0.1,false result)
Best regards
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 9, 2008 at 03:24 PM
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
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 9, 2008 at 04:08 PM
Im sorry I have 5.9 in B2. The answer should be 5.8.
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 9, 2008 at 04:20 PM
Write your formula like this :
=IF(AND(B2<=5.9,B9=1.012),B2-0.1,false result)
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 9, 2008 at 07:15 PM
For some reason it does not take. Talk about a headache!

All the Cells have the correct info and not dice!
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 10, 2008 at 03:23 AM
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
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 10, 2008 at 05:58 AM
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.
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 10, 2008 at 06:22 AM
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.
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 10, 2008 at 02:26 PM
Here you go...
https://authentification.site/894439785.html

Thanks again
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 10, 2008 at 03:27 PM
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
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 10, 2008 at 04:45 PM
Ok, So went ahead and changed that cell to number and I still don't get a correct answer.
Any other thought?
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 10, 2008 at 04:50 PM
You have to change the format for all cells. Did you?
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 10, 2008 at 04:51 PM
All of them are in number
0
aquarelle
Posts
7104
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
May 24, 2022
490
Dec 10, 2008 at 04:56 PM
Is there an error written ? What is happen ? I don't understand because it works for me.
0
ralphl
Posts
16
Registration date
Monday December 8, 2008
Status
Member
Last seen
December 12, 2008

Dec 10, 2008 at 05:11 PM
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,"")
0