Excel If And Statements

February 13, 2014 at 12:02:27
Specs: Windows 7
I have reviewed the formula but only the final FALSE statement validates. Below is the formula:

=IF(AND(AD10="1",AF10="0.5"),"$145.00",IF(AND(AD10="1",AF10>"0.5"),(((AF10*2)*93)+15),IF(AND(AD10="0",AM10="1",AO10="0.5"),"$145.00",IF(AND(AD10="0",AM10="1",AO10>"0.5"),(((AO10*2)*93)+15),IF(AND(AD10="0",AM10="0",AX10="0.5"),"$145.00",(((AX10*2)*93)+15))))))

What am I missing?



See More: Excel If And Statements

Report •


#1
February 13, 2014 at 17:10:51
I haven't tried to test your formula,
but from just looking at it I would advise removing all the quotes around your numbers,
IE: "145.00" and "0.5"

The quotes tells Excel that it is TEXT not a Number.

MIKE

http://www.skeptic.com/


Report •

#2
February 14, 2014 at 06:30:50
Thank you Mike...

When I removed the quotes, I got this error message, #NAME...


Report •

#3
February 14, 2014 at 08:15:57
When I entered your formula into cell A1, in a blank spread sheet, I get 15
which would seem to be the default answer.

As for the #NAME error, make sure all the words AND are spelled correctly
that would seem to be the only reason for the error message in your formula.

Also, if you re-entered the formula by hand, you do not need the $ signs, remove them also.

EDIT ADDED:

Also make sure all your Number 1's and 0's are numbers,
not lower case L and letter O's


.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
February 14, 2014 at 13:45:46
After your corrections, the formula worked. However, I had to expand and am still getting the #NAME error for this:

=IF(AND(AD2="1",AF2=".5"),"$145.00",(IF(AND(AD2="1",AF2>".5"),(((AF2*2)*93)+15),(IF(AND(AD2="0",AF2=”.5”,AM2="1",AO2=”.5”),"$145.00",(IF(AND(AD2="0",AM2="1",AO2>AF2),(((AO2*2)*93)+15),(IF(AND(AD2="0", AF2=”.5”,AM2="0",AO2=”.5”,AV2="1",AX2=".5"),"$145.00",(IF(AND(AD2=”0”,AM2=”0”,AV2=”1”,AX2>AF2),(((AX2*2)*93)+15),(AF2*2)*93)+15))))))))))

Suggestions?


Report •

#5
February 14, 2014 at 14:39:58
Seriously, is that your formula?

REMOVE THE QUOTES

You even have two different types of quotes in this one.
Are you using a Word-processor?

Your original formula worked for me.
My previous response:

When I entered your formula into cell A1, in a blank spread sheet, I get 15
which would seem to be the default answer.

This one does the same thing, returns 15

MIKE

http://www.skeptic.com/


Report •

#6
February 14, 2014 at 18:37:04
This made me laugh. No word processor but I've been going back and forth between word so I can see it better. When I remove the quotes, I get either 108 or #NAME. I've checked my AND statements and think they're ok. So...my thought is I've cancelled out all my arguments somehow.

(I swear I do not have a word processor or a cup holder on my computer and the error is ALL PEBCAC)...


Report •

#7
February 14, 2014 at 20:05:32
I get either 108 or #NAME.

See here for why you would get a #NAME error in Excel, this is for 2007, but
it hasn't changed much between versions:

http://office.microsoft.com/en-us/e...

As you can see the only one that may apply to your formula would be:
The name of a function that is used in a formula is not spelled correctly.

You only have two function, IF and AND so unless your doing something else, they would seem to be the culprit.
But, you will also get the NAME error if you use a Letter in place of a number, therefore I warned to make sure all your Number 1's and 0's are numbers,
not lower case L and letter O's.
This also applies to your Column Letters, IE AOxx not A0xx,
the first one is the letter O the second is the number Zero.

Since you are getting some type of answer, is it the correct one you expect?
What are you doing different when you get the #NAME error?

No word processor but I've been going back and forth between word so I can see it better.

You would be better served NOT to use Word, which uses different characters, as seen by the two styles of quotes, then Excel.
So if your not directly entering the formula in Excel, use an editor like Notepad or JEDIT.

MIKE

http://www.skeptic.com/


Report •

#8
February 14, 2014 at 20:52:39
When I paste the latest formula into a cell I get #NAME.

The formula evaluator shows the error coming from this:

AF2=”.5”

As Mike noted, there are different styles of quotes in your formula and as soon as Excel encounters those backward quotes it doesn't recognize it as a text string or a number, thus you get a #NAME error.

In addition, you have repetitive clauses, unnecessary parenthesis, etc.

I broke your formula down into it's parts and pieces and if I'm not mistaken, these are your "IF's" for each result:

(Note: I'm dropping all quotes and extra parentheses)

For 145:
AND(AD2=1,AF2=.5)
AND(AD2=0,AF2=.5,AM2=1,AO2=.5)
AND(AD2=0, AF2=.5,AM2=0,AO2=.5,AV2=1,AX2=.5)

For AF2*2*93+15
AND(AD2=1,AF2>.5)

For AO2*2*93+15
AND(AD2=0,AM2=1,AO2>AF2)

For AX2*2*93+15
AND(AD2=0,AM2=0,AV2=1,AX2>AF2)

Your final value_if_false appears to be AF2*2*93+15

That also happens to be the desired result for AND(AD2=1,AF2>.5). That's a bit confusing, but if that's what you want, OK.

So if I take all of your criteria and desired results and put them in a formula, I get this:

=IF(AND(AF2=0.5,OR(AD2=1,AND(AD2=0,AM2=1,AO2=0.5),
AND(AD2=0,AM2=0,AO2=0.5,AX2=0.5))),145,
IF(AND(AD2=1,AF2>0.5),AF2*186+15,
IF(AND(AD2=0,AM2=1,AO2>AF2),AO2*186+15,
IF(AND(AD2=0,AM2=0,AV2=1,AX2>AF2),AX2*186+15,
AF2*186+15))))

You will have to test it to see if you get the results you expect.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
February 19, 2014 at 16:12:31
Thank you all for your assistance.

I am still getting 108 as an answer and that should never be the answer. The answer will either be 145 or something >=201.


Report •

#10
February 19, 2014 at 20:28:34
How could we possibly know what your answer should be without know what your inputs are?

For example, let's look at this clause:

IF(AND(AD2=1,AF2>0.5), AF2*2*93+15,...)

Assume AD2 = 1 and AF2=0.6

Since both AND arguments are TRUE, the IF is TRUE, so that part of the formula will return .6*2*93+15 = 126.6

We can find all sorts of combinations of inputs that will satisfy many of your IF clauses and return all sorts of different answers.

Unless you tell us what the limitations on your inputs are, we don't know how to resolve the "I am still getting 108 as an answer and that should never be the answer" issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#11
February 21, 2014 at 09:00:15
Equation Criteria
Basically, I am trying to auto populate the cost for transportation from a pickup location to the nearest three airports for a few thousand locations. I have added a screen shot of my spreadsheet and the location of the equation. Below is the criteria spelled out:

* If the travel time between airport and pick up location is less than .5 & we have an affiliate in the area, the cost should be 145.
* If the time is more than .5 & we have an affiliate in the area, the cost should be the time multiplied by 2, then 93 per hour, plus 15, (to account for drive time back and regulatory fees).
* No matter the travel time, if there is no affiliate in the area, then I need to look at the next airport and if it has an affiliate in the area.
* If it does, then I need to multiply the greatest travel time between the two airports by 2, then 93 per hour, plus 15.
* If it doesn't, then I need to look at the next airport and if it has an affiliate in the area and repeat the process.


Report •

#12
February 21, 2014 at 11:46:15
It just occurred to me:

Some of the cells I have identified as 1 or 0.5 contain formulas. I do not know if this makes a difference on how this formula calculates.


Report •

#13
February 21, 2014 at 13:31:43
I'll have to play with this later tonight.

An IF function should not care if the value it is checking is a hardcoded value or the result of a formula. It checks the actual value.

The thing to be aware of is that 0.49 might display as 0.5, but the IF will see it as 0.49.

In other words, if the result of a formula is 0.49 but the cell is formatted to display a single decimal point, it will display 0.5. However, if the IF says =IF(A1 = 0.5, TRUE, FALSE) then the result will be FALSE because 0.49<>0.5 regardless of what the cell displays.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
February 21, 2014 at 13:49:39
Thank you for taking the time!

The "Time" cells do have a ROUNDING formula, so what you said above makes absolute sense.


Report •

#15
February 21, 2014 at 15:02:38

Formulas for blank AD2, AM2, & AX2:

=IF(ISTEXT(AE2),"1","0")

=IF(AC2<30,".5",ROUNDUP(AC2/60,0))


Report •

#16
February 21, 2014 at 15:12:33
Before I do any work, I have to understand why you use quotes around your numbers.

It's been pointed out (numerous times) that the quotes cause Excel to assume that the numbers are TEXT.

So tell us, why do you use quotes in your formulas?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#17
February 21, 2014 at 16:25:43
I just noticed this actually. I have only been looking for quotes in the one formula. I have updated the formulas below:

Affiliate Verification:

=IF(ISTEXT(AE2),1,0)

Time:

=IF(AC2<=30,0.5,(AC2/60))

Rate:

=IF(AND(AD2=1,AF2=0.5),120,IF(AND(AD2=1,AF2>0.5),(((2*(ROUNDUP(AF2,2)))*93)+15),IF(AND(AD2=0,AM2=1,AM2>AF2),(((2*(ROUNDUP(AM2,2)))*93)+15),IF(AND(AD2=0,AM2=0,AV2=1,AV2>AF2),(((2*(ROUNDUP(AX2,2)))*93)+15),"Out of Area"))))

While it is working for most areas, there are still some rates that are not populating based on the presence of a 1 or 0 in AD, AM, or AV.

Examples:

Verification 	Affiliate 1	Time	Sedan 1	Airport 2	Distance 2	Verification	Affiliate 2	Time 2	Sedan 2	Int. Airport 	Distance 3	Verification	Affiliate 3	Time 3	Sedan 3
0		            0.98	$108.00	MOD	                89	       0		          1.48   	SMF	           10	          1	          SAC-1	         0.50	


Report •

#18
February 21, 2014 at 20:36:57
Still confused...

In Response #15 you said:

Formulas for blank AD2, AM2, & AX2:

=IF(ISTEXT(AE2),"1","0")


=IF(AC2<30,".5",ROUNDUP(AC2/60,0)

3 cells listed, 2 formulas given. See my confusion?

Then in Response # 17 you said:

Affiliate Verification:

=IF(ISTEXT(AE2),1,0)

Time:

=IF(AC2<=30,0.5,(AC2/60))

Your spreadsheet screen capture does not show any cells listed as Affiliate Verification Are those the cells with no Column labels, AD, AM and AV?

If so, are the formulas in all 3 Verification cells supposed to refer AE2 or is there a different formula, referring to a different cell, in each Verification cell?

Are the formulas in all 3 Time cells supposed to refer to AC2 or is there a different formula, referring to a different cell, in each Time cell?

Please read the following paragraph in the spirit it is offered:

We want to help, but if we have to keep dragging bits and pieces of information out of you, we are eventually going to give up. We don't know what you are trying to, so it's hard for us to test your formula - especially a formula as convoluted as yours - without knowing what the inputs are and what the expected outputs should be. When posting in a help forum such as this one, you should really try to supply as many details about your formula as you can. That includes example inputs and the expected output based on those inputs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
February 21, 2014 at 21:31:39
I am grateful for the help. I apologize for the poor input. This was a first time request and I will gather better data next time.

Thank you again.


Report •

#20
February 22, 2014 at 08:19:40
You haven't answered the questions posed in Response #18.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question