If Function Question

[Jesse]

I have a simple function to test if the value of cell D6 is less than 50.

=IF(D6 < $H$6,D6,"")

where H6 = '50' (without hyphens)

However I would like to be able to enter '<50' in cell H6 so I can control
the operator (<,>,<>,etc.) without editing the formula.

I have tried this but get #Value! error

=IF(D6 &$H$6,D6,"")

where H6 = '<50' (without hyphens)

Help is appreciated.

 

[Jason Morin]

You could create a UDF or use something like:

=IF(EVAL=TRUE,D6,"")

where EVAL is a defined name (Ctrl+F3) equal to:

=EVALUATE(Sheet1!$D$6&Sheet1!$G$6&Sheet1!$H$6)

G6 holds the operator.

This method still works but will be eventually phased out
with newer versions of Excel. Also, if you're using
something lower that Excel 2002, be sure to read this
warning:

"XLM function calls in defined names are oh so clever.
However, in versions of Excel prior to 2002/XP, if you
copy a cell with a formula referring to an XLM function -
no matter how indirectly - then paste into another
worksheet, Excel will crash with complete data loss."

HTH
Jason
Atlanta, GA

 

[Aladin Akyurek]

=IF(COUNTIF(D6,H6),D6,"")

or just:

=COUNTIF(D6,H6)*D6

where H6 houses expressions like <=50, <50, >70, etc.

 

[Jesse]

This method is good, however, it freaks out when I need to calculate the IF
whereas to find a number between 2 numbers like:

D6>50,100>D6

I'll keep hacking at it.

Thanks for your input.

 

[Harlan Grove]

=IF(COUNTIF(D6,H6),D6,"")

or just:

=COUNTIF(D6,H6)*D6

...

Or just

=SUMIF(D6,H6,D6)