Change Boolean / Logical Operator using Input Cell

[Sam via OfficeKB.com]

Hi All,

I have a formula filled down a column that tests for the presence of various
numeric values in a row.
I would like to manipulate the boolean / logical operator within the cell
formula on the fly by using separate input cells for ">" (greater than) sign
etc; and numeric value.

In the formula below, I would like to use 2 separate input cells to house the
greater than sign and the numeric value, in this case 0.

=IF(AND(Q18=$Q$4,R18=$R$4,S18=$S$4,T18=$T$4,OFFSET($Q18,1,0,,1)>0),"Yes","")

I've tried using 2 separate cells but not sure how the logical operator
should be entered or formatted?

Thanks
Sam

 

[Bernie Deitrick]

Sam,

You need to use an IF to test the value and return the correct evaluation:

=IF(AND(Q18=$Q$4,R18=$R$4,S18=$S$4,T18=$T$4,IF(C2=">",OFFSET($Q18,1,0,,1)>0,OFFSET($Q18,1,0,,1)<0)),"Yes","")


HTH,
Bernie
MS Excel MVP

 

[Sam via OfficeKB.com]

Hi Bernie,

Thank you very much for your time and assistance. Your addition to the
formula has done the job brilliantly, and is very much appreciated. It works
Great!

I've been trying to sort that for more hours than I care to remember!

Cheers,
Sam