Excel formula using non-literal values

[jbdmom]

I have defined a formula that tests the value in 3 columns. If the value in
each column passes the test, the formula calculates a value for another
column. e.g. =IF(AND(I7<750,J7<100,K7<50000),C7*0.06,"0")

I've noticed that if the value of the column being tested contains a value
symbol (< or >), the formula does not calculate. It appears the formula
requires a literal value ("10" vs. "<10").

Is there a way around this?

Thank you for your time.

 

[Peo Sjoblom]

It requires a number which is logical but if you mean that
the cell holds the text string <750 and not 750 another cell holds <100 etc
then you would need to use


=IF(AND(I7="<750",J7="<100",K7="<50000"),C7*0.06,0)


and you can only use the < meaning less than for real numbers






--


Regards,


Peo Sjoblom

 

[jbdmom]

Thanks Peo.

My problem is the values can range from "<10" to ">560000" and any real
number in between. But if it's less than < 750 (which <10 is), I wanted it
to auto-calculate. I'll just propose we report it as 10 if it's "<10" for
calculation purposes. It might be OK.

Thanks again.

 

[ShaneDevenshire]

Hi,

This might do what you appear to want:

=IF(AND(IF(ISTEXT(I7),--MID(I7,2,5),I7)<750,IF(ISTEXT(J7),--MID(J7,2,5),J7)<100,IF(ISTEXT(K7),--MID(K7,2,5),K7)<50000),IF(ISTEXT(I7),MID(I7,2,5),I7)*0.06,0)