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?

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

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

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

message edited by mmcconaghy

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?

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

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)...

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

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_falseappears to be AF2*2*93+15That 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

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.

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

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.

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.

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.

Thank you for taking the time! The "Time" cells do have a ROUNDING formula, so what you said above makes absolute sense.

Formulas for blank AD2, AM2, & AX2:=IF(ISTEXT(AE2),"1","0")=IF(AC2<30,".5",ROUNDUP(AC2/60,0))

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.

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

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.

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.

You haven't answered the questions posed in Response #18.

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

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History