View Full Version : Conditional statement does not work
steveWG
06-27-2012, 02:01 PM
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
06-27-2012, 02:33 PM
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
06-27-2012, 02:41 PM
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
06-27-2012, 03:00 PM
Thanks for the help, however, I am continuing to get the same error.
Steve
steveWG
06-27-2012, 03:10 PM
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
06-27-2012, 03:13 PM
Sorry...now its working...
Thanks a lot
Steve
steveWG
06-27-2012, 10:24 PM
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
06-28-2012, 12:16 AM
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
06-28-2012, 08:52 AM
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
06-28-2012, 08:57 AM
Can't see anything obvious Steve. Can you post your workbook?
steveWG
06-28-2012, 10:20 AM
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
06-28-2012, 10:22 AM
Your formula in H5 down should be
=criteria1
but why you are creating it as a name is beyond me.
steveWG
06-28-2012, 10:44 AM
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
06-28-2012, 10:55 AM
Finally I have it working
Thanks again, I know this was and arduous process.
Steve
Bob Phillips
06-28-2012, 11:21 AM
What changed between posts #13 and #14?
steveWG
06-28-2012, 01:54 PM
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
06-28-2012, 02:08 PM
=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)))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.