#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",""))

 

[arthurbr]

That is because the last result of each part of your formulas could be "".
If anyone ( or both) of the two parts returns "" ( which is an empty text string) you get the VALUE error because you are adding a value to text or two texts together
Perhaps best to replace the "" with 0

 

[jgold20]

My understanding is that "" returns nothing (blank, no value). I changed it to 0 and still received the #value error.

When I just have the first if only, if there is no value (nothing is in the cell), it remains empty and no message is displayed. If the cell is 200 or >, no message is displayed. This is how it is supposed to work.

 

[MARK858]

My understanding is that "" returns nothing (blank, no value).

"" is not "blank", it is an empty string and so text. Put ="" in cell A1 and put in cell B1 =ISTEXT(A1) and see if you get TRUE or FALSE. Then in C1 put =ISBLANK(A1) and see if you also get TRUE or FALSE.

As for your question did you put 0 in the formula or "0"?

If the first then please post a sample of your data (please see my signature block for some links to ways of posting usable screenshots on the forum).

 

[jgold20]

=IF(OR(A3="GE",A3="GEL",A3="SR",A3="SRL",A3="VIL",A3="VP"),IF(AND(B15>0,B15<200),"CF < 200",0)) +
IF(OR(A3="B",A3="W"),IF(AND(B15>0,B15<286),"CF < 286",0))
[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"]B[/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"]5[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 116, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

So what cells are they supposed to be? and there should be 3 cells at least (A3,B15 and seeing as you have posted the cell with the value error, that cell).

 

[jgold20]

Being A3 is B, where I have the #value ! it should say Cf < 286. I am testing the code in A3 and the value in b15 which I showed is number "5" with the IF statement being placed in B16 to show the result of the test

 

[MARK858]

Excel Workbook
	A	B
3	B
4
14
15		5
16		CF
Sheet3

 

[jgold20]

That is working, however, when there is nothing in b15, I want B16 to be blank, not have a 0 in it. I placed "" instead of the 0, and it works. I have other values in a3 - GEL, BL, etc. and when they are selected, I am getting a message in B16 of FALSE. I would think it would not display anything.

 

[MARK858]

Excel Workbook
	A	B
3	klo
4
14
15		15
16
Sheet3