Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula using non-literal values
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula using non-literal values
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula using non-literal values
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. "Peo Sjoblom" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula using non-literal values
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),I 7)*0.06,0) -- Thanks, Shane Devenshire "jbdmom" wrote: 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. "Peo Sjoblom" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number to words literal | Excel Worksheet Functions | |||
Literal quotation marks retained from a formula | Excel Discussion (Misc queries) | |||
How to use literal quotes with concatenation | Excel Discussion (Misc queries) | |||
formula is displayed as literal text instead of formula result | Excel Discussion (Misc queries) | |||
Referencing Literal Arrays | Excel Worksheet Functions |