#value error - I thought I understood the IF statement

[jgold20]

When I have the first line of code, the if statement works. When I add the second line, I get a #value error for all selections from the drop-down box. I do not understand what id wrong with the logic?

=IF(OR(A3="GE",A3="GEL",A3="SR",A3="SRL",A3="VIL",A3="VP"),IF(AND(B15>0,B15<200),"CF < 200","")) +
IF(OR(A3="B",A3="W"),IF(AND(B15>0,B15<286),"CF < 286",""))

 

[jgold20]

This is working if I replace the 0 with "". However, there are some other values in A3 when there are selected, I receive an FALSE in B16. I thought they would just be ignored?

 

[MARK858]

This is working if I replace the 0 with "". However, there are some other values in A3 when there are selected, I receive an FALSE in B16. I thought they would just be ignored?

Really? what 0 did you replace in the formula in post number 10? and why are you getting FALSE with other values when the klo in A3 in post number 10 which isn't in your criteria produces "" in B16?

 

[jgold20]

I am using the number zero. I have no idea why I am receiving the false.

 

[MARK858]

Again are you using the formula from post 10? If you are (and I can't see how you can be if you have the 0 as a fail result as there isn't one in the formula) post your data where it fails!

 

[jgold20]

1) When b15 is blank, b16 shows a zero (I want b15 to remain blank)
2) if there is a selection that is not in any of the if statements, b16 displays FALSE

[TABLE="width: 111"]
<colgroup><col width="111" style="width: 83pt;"></colgroup><tbody>[TR]
[TD="class: xl70, width: 111, align: center"]Company Code[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]BL[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 116"]
<colgroup><col width="116" style="width: 87pt;"></colgroup><tbody>[TR]
[TD="class: xl72, width: 116, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl73, width: 116, align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
=IF(OR(A3="D",A3="GE",A3="H",A3="K",A3="L",A3="LG",A3="M",A3="N",A3="NO",A3="Q",A3="R",A3="SR",A3="V",A3="VI",A3="W",A3="X",A3="Y"),IF(AND(B15>0,B15<200),"CF < 200",0),
IF(OR(A3="B",A3="W"),IF(AND(B15>0,B15<286),"CF < 286",0),
IF(OR(A3="MD",A3="MN"),IF(AND(B15>0,B15<286),"CF < 300",0))))

 

[MARK858]

That is not the formula from post number 10! I will leave this thread alone as you obviously aren't going to try what I posted.

 

[jgold20]

The code from 10 was used and it has the same problems as the code from 15. I added different values for A# and a new test is needed for <300

The one from 15 when "" is used instead of zero, leaves b16 blank when there is no value in b15

 

[MARK858]

The one from 15 when "" is used instead of zero, leaves b16 blank when there is no value in b15

Yes, as you requested that is what the formula from post 10 does

when there is nothing in b15, I want B16 to be blank, not have a 0 in it

and the FALSE as requested displays as blank with the formula in post 10.

I have no interest in adding in the new extra condition as the formula in post 15 ignores totally what I posted in post number 10

 

[jgold20]

Understood about the new condition, using your code from 15, does not do what I am looking for - when b15 is empty, b16 should be empty.When a value that is not in the if statement is in a3, B16 should be empty, which it clearly is not.

[TABLE="width: 125"]
<colgroup><col width="125" style="width: 94pt;"></colgroup><tbody>[TR]
[TD="class: xl72, width: 125, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl73, width: 125, align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 125"]
<colgroup><col width="125" style="width: 94pt;"></colgroup><tbody>[TR]
[TD="class: xl72, width: 125, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: center"] [/TD]
[/TR]
[TR]
[TD="class: xl73, width: 125, align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

using your code from 15, does not do what I am looking for

Yet again the code in post 15 is not my code and not the formula you should be amending. That was the formula in POST 10!!!!!!!! why do you persist in stating post 15???

Seeing as you seem to not know where to find post 10 below is the formula from post number 10 amended to your new condition...

Excel Workbook
	A	B
3	MN
4
14
15		199
16		CF
Sheet3

Goodnight!