Conditional statement does not work

[steveWG]

I am attempting to use a conditinal statement in in the Insert|Name|Define function's refers to dialogue box that will check to see if data is in one cell and if so, use one criteria and if not another as follows:



if a5="" then
=IF(AND(Sheet1!G5=90801),150,IF(AND(Sheet1!G5=90804),65,IF(AND(Sheet1!G5=90 806),125,IF(AND(Sheet1!G5=90846),125,IF(AND(Sheet1!G5=90847),125,IF(AND(She et1!G5=90853),60))))))
else
=IF(AND(Sheet1!G5=90801),200,IF(AND(Sheet1!G5=90804),100,IF(AND(Sheet1!G5=9 0806),175,IF(AND(Sheet1!G5=90846),175,IF(AND(Sheet1!G5=90847),175,IF(AND(Sh eet1!G5=90853),110))))))end if




The error I am getting is "A value used in the formula is of the wrong data type". However, if I run either formula by itself (without the conditional statements, they work.

Any help would be much appreciated.

Steve

[Bob Phillips]

Do you mean



=IF(A5="",
IF(AND(Sheet1!G5=90801),150,IF(AND(Sheet1!G5=90804),65,IF(AND(Sheet1!G5=908 06),125,IF(AND(Sheet1!G5=90846),125,IF(AND(Sheet1!G5=90847),125,IF(AND(Shee t1!G5=90853),60)))))),
IF(AND(Sheet1!G5=90801),200,IF(AND(Sheet1!G5=90804),100,IF(AND(Sheet1!G5=90 806),175,IF(AND(Sheet1!G5=90846),175,IF(AND(Sheet1!G5=90847),175,IF(AND(She et1!G5=90853),110))))))

[Bob Phillips]

You can simplify it as well


=INDEX(IF(A5="",{150,65,125,125,125,60},{200,100,175,175,175,110}),
MATCH(Sheet1!G5,{90801,90804,90806,90846,90847,90853},0))

[steveWG]

Thanks for the help, however, I am continuing to get the same error.

Steve

[steveWG]

I must be doing something wrong since when I try the simpler code, I am getting the error "A value is not available to the formula or function"..

Steve

[steveWG]

Sorry...now its working...

Thanks a lot

Steve

[steveWG]

One more question:
The below code is working, but I need to do one more thing. When the code is in an otherwise blank cell, I get this #NA displayed in the cell and the error message "a value is not available to the formula or function. If possible I would like to showthe value as 0:


=INDEX(IF(Sheet1!A5="",{150,65,125,125,125,60,80,150,150,0},{190,105,165,165,165,100,120,190,190, 0}), MATCH(Sheet1!G5,{90801,90804,90806,90846,90847,90853,90901,96100,96118,""},0))



I inserted the amount of 0 at the end of the Index string, and the double quotes at the end of the Match string, but this did not work.

Thanks again

Steve

[Bob Phillips]

If you have Excel 2007 or 210, you can use


=IFERROR(INDEX(IF(Sheet1!A5="",{150,65,125,125,125,60,80,150,150,0},{190,105,165,165,165,100,120,190,190, 0}),
MATCH(Sheet1!G5,{90801,90804,90806,90846,90847,90853,90901,96100,96118,""},0)),"")

if not use


=IF(Sheet1!G5="","",INDEX(IF(Sheet1!A5="",{150,65,125,125,125,60,80,150,150,0},{190,105,165,165,165,100,120,190,190, 0}),
MATCH(Sheet1!G5,{90801,90804,90806,90846,90847,90853,90901,96100,96118,""},0)))

[steveWG]

Thanks again....

I am using Excel 2003. When I try the below code, I am getting the erro "A value used in the formula is of the wrong data type". Since the cell's property is Generl, I don't undersstand why I am getting this error.



=IF(Sheet1!G5="","",INDEX(IF(Sheet1!A5="",{150,65,125,125,125,60,80,150,150,0},{190,105,165,165,165,100,120,190,190, 0}),
MATCH(Sheet1!G5,{90801,90804,90806,90846,90847,90853,90901,96100,96118,""},0)))


Thanks again for hanging in there with me,

Steve

[Bob Phillips]

Can't see anything obvious Steve. Can you post your workbook?

[steveWG]

I think I have a headache...didn't realize I could do this with the original thread....here is tthe correct version of the workbook...the above one is wrong

Steve

[Bob Phillips]

Your formula in H5 down should be

=criteria1

but why you are creating it as a name is beyond me.

[steveWG]

Thanks, changed it, however I am still getting that error in blank cells below where there is no data in the adjacent G coumn, which is populated by a drop down list and triggers the data for column H. Not sure what you mean by creating a name.

[steveWG]

Finally I have it working

Thanks again, I know this was and arduous process.

Steve

[Bob Phillips]

What changed between posts #13 and #14?

[steveWG]

The workbook had a small change.

Can I go to the well one more time? The person I am doing this for (my wife) has decided she would like one more bit of criteria in this. If cell M5=CCOF, she would then like the data in cell I5 to equal 0. I have tried this:



=IF(Sheet1!M5="CCOF",Sheet1!I5=0),IF(Sheet1!F5="","",INDEX(IF(Sheet1!IV5="",{150,65,125,125,125,60,80,150,150,40,0},{150,65,125,125,125,60,80,150,150, 40,0}), MATCH(Sheet1!F5,{90801,90804,90806,90846,90847,90853,90901,96100,96118,"No Show/LT Fee",""},0)))



But it creates a circular reference.

Hopefully for the last time, thanks

Steve

[Bob Phillips]

=IF(OR(Sheet1!M5<>"CCOF",Sheet1!I5<>0,Sheet1!F5=""),"",
INDEX(IF(Sheet1!IV5="",{150,65,125,125,125,60,80,150,150,40,0},{150,65,125,125,125,60,80,150,150, 40,0}),
MATCH(Sheet1!F5,{90801,90804,90806,90846,90847,90853,90901,96100,96118,"No Show/LT Fee",""},0)))