IF Function: Use of Logical Operators Located in a Cell

[CMDMA03]

Is it possible to outline your logical expressions (e.g., >=, <=, etc...) in a cell and then reference that cell in an IF function?

Example:
I have a percentage score in B5 (e.g., 10%), and a target score that contains logical operators in B4 (e.g., >=9.8%). I'd like to be able reference the two cells in an IF function and produce a binary result, but I get #NAME or #VALUE errors when I've tried the syntax below. I've searched and can't seem to find much on this anywhere. Maybe I'm not searching with the right language. Does anyone have suggestions? Thanks in advance.

=IF(B5&$B$4,0,1)

=IF(B5$B$4,0,1)

=IF(B5AND($B$4),0,1)

 

[Scott Huish]

=countif(b5,b4)

 

[BiocideJ]

Is it possible to outline your logical expressions (e.g., >=, <=, etc...) in a cell and then reference that cell in an IF function?

Example:
I have a percentage score in B5 (e.g., 10%), and a target score that contains logical operators in B4 (e.g., >=9.8%). I'd like to be able reference the two cells in an IF function and produce a binary result, but I get #NAME or #VALUE errors when I've tried the syntax below. I've searched and can't seem to find much on this anywhere. Maybe I'm not searching with the right language. Does anyone have suggestions? Thanks in advance.

=IF(B5&$B$4,0,1)

=IF(B5$B$4,0,1)

=IF(B5AND($B$4),0,1)

Scott's formula will return 1 if both are TRUE OR if both are FALSE.

I read your question as if B4=TRUE AND B5=TRUE then 0 else 1 which would be written as
=IF(AND(B4,B5),0,1)

 

[Snakehips]

I'm reading it completely differently and would see this requiring a UDF ?????

Eg Copy the following to a Code module in the vb editor...

Function Eval(R1 As Range, R2 As Range) As Boolean
Eval = Evaluate("=" & R1.Value & R2.Value)
End Function

Then use the UDF as part of an in cell formula.....

Excel 2007
	B	C	D	E
4	10%	9.70%	34%	5%
5	>=9.8%	>=9.8%	<9.8%	<9.8%
6	TRUE	FALSE	FALSE	TRUE
Sheet6

Cell Formulas
Range	Formula
B6	=IFERROR(Eval(B4,B5),"")

 

[Rick Rothstein]

Scott's formula will return 1 if both are TRUE OR if both are FALSE.

What do you mean "both are TRUE OR if both are FALSE"? The OP said...

"I have a percentage score in B5 (e.g., 10%), and a target score that contains logical operators in B4 (e.g., >=9.8%)."

Where do see TRUE and FALSE at? As far as I can see, Scott's formula appears to do exactly what the OP requested.

 

[Scott Huish]

This doesn't require VBA.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >>=9.8%</td><td >>=9.8%</td><td ><9.8%</td><td ><9.8%</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">10%</td><td style="text-align:right; ">9.70%</td><td style="text-align:right; ">34%</td><td style="text-align:right; ">5.00%</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">1</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B6</td><td >=COUNTIF(B5,B4)</td></tr><tr><td >C6</td><td >=COUNTIF(C5,C4)</td></tr><tr><td >D6</td><td >=COUNTIF(D5,D4)</td></tr><tr><td >E6</td><td >=COUNTIF(E5,E4)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

 

[Snakehips]

This doesn't require VBA.......

Scott, you are, of course absolutely correct!!
When I tried your formula I had B4 and B5 transposed compared to the correct syntax and thus it resolved to a row of zeros. And I was daft enough to believe it wasn't working. Which it wasn't but not for the reason I had imagined...

 

[BiocideJ]

Actually, now that I re-read the OP, I see what Scott was doing and his solution actually was pretty much what the OP is asking for (except I think it may return the 0/1 values backwards to the OP request).

If that is the case, the following may work...
=IF(COUNTIF(B5,B6)=1,0,1)

Hopefully, the OP responds to resolve the mystery.

 

[CMDMA03]

I'm trying to replace the hard coded logical expression in the function bar with a reference to B4 (or $B$4), so that I can modify one value (and have it apply to all functions in Column C), as opposed to modifying the formulas for the entire Column C. I'm just looking for ease of updating. I do appreciate the feedback.

Excel 2007

 

[CMDMA03]

Actually, now that I re-read the OP, I see what Scott was doing and his solution actually was pretty much what the OP is asking for (except I think it may return the 0/1 values backwards to the OP request).

If that is the case, the following may work...
=IF(COUNTIF(B5,B6)=1,0,1)

Hopefully, the OP responds to resolve the mystery.

The IF(COUNTIF()... worked like a charm. Thanks all.