strip out < sign and then calculate

[smj2]

Hi, I am doing a check on a cell value to see if the < sign is
present or not using the LEFT function. eg if left = "<" then return
me the number to the right of the < sign and if < is not present then
just return me the complete cell value (a number).

the reason for doing this is that i then need to check if the value is

another value.

eg, cell shows: <250. the LEFT spreadsheet function strips out the <
sign and returns me 250. then i check if that value is > another
value. However even when the cell is showing <250, the formula below
returns "T".. meaning it is saying 250>3000. I suspect it's because
once you use RIGHT or LEFT functions then it converts these to text.

actual formula (where the value in D22 is <250) :

=IF(

(IF(LEFT(D22)="<",RIGHT(D22,LEN(D22)-1),D22))>3000,

"T","F")

if i just put:

IF(LEFT(D22)="<",RIGHT(D22,LEN(D22)-1),D22)

....in a cell it correctly returns 250 and then in a separate cell if i
check if this value is > 3000 it also returns TRUE .. not correct.

any help/advice appreciated very much. thank you

 

[Rick Rothstein \(MVP - VB\)]

Hi, I am doing a check on a cell value to see if the < sign is

present or not using the LEFT function. eg if left = "<" then return
me the number to the right of the < sign and if < is not present then
just return me the complete cell value (a number).

the reason for doing this is that i then need to check if the value is

eg, cell shows: <250. the LEFT spreadsheet function strips out the <
sign and returns me 250. then i check if that value is > another
value. However even when the cell is showing <250, the formula below
returns "T".. meaning it is saying 250>3000. I suspect it's because
once you use RIGHT or LEFT functions then it converts these to text.

actual formula (where the value in D22 is <250) :

=IF(

(IF(LEFT(D22)="<",RIGHT(D22,LEN(D22)-1),D22))>3000,

"T","F")

if i just put:

IF(LEFT(D22)="<",RIGHT(D22,LEN(D22)-1),D22)

...in a cell it correctly returns 250 and then in a separate cell if i
check if this value is > 3000 it also returns TRUE .. not correct.

Your separated out formula is returning 250 as text, not a number... and you
are testing if that is greater than 3000. Off the top of my head, I am not
sure what mechanism is at work in this comparison, but you can see the
problem by entering this formula in any cell...

=("2">2)

which returns True. You can correct your problem by placing two unitary
minus signs in front of the RIGHT function (the math operation forces the
text value to a numerical value by multiply minus one times minus one, which
is plus one, by the numerical text string returned by your RIGHT function
call)...

=IF((IF(LEFT(D22)="<",--RIGHT(D22,LEN(D22)-1),D22))>3000,"T","F")

Rick

 

[Rick Rothstein \(MVP - VB\)]

=IF((IF(LEFT(D22)="<",--RIGHT(D22,LEN(D22)-1),D22))>3000,"T","F")

By the way, we can simplify that formula a little bit...

=IF(--SUBSTITUTE(D22,"<","")>3000,"T","F")

Simply remove the "<" symbol if one is present and then do your comparison
test.

Rick

 

[smj2]

By the way, we can simplify that formula a little bit...

=IF(--SUBSTITUTE(D22,"<","")>3000,"T","F")

Simply remove the "<" symbol if one is present and then do your comparison
test.

Rick

Many thanks.

I wish I knew about the SUBSTITUTE function before i wrote the
following:

=IF(OR(LEFT(H17)="<",LEFT(J17)="<",LEFT(L17)="<"),"<"
&FIXED(STDEV(IF(TYPE(H17)=1,H17,RIGHT(H17,LEN(H17)-1)),IF(TYPE(J17)=1,J17,RIGHT(J17,LEN(J17)-1)),IF(TYPE(L17)=1,L17,RIGHT(L17,LEN(L17)-1))),
2),STDEV(IF(TYPE(H17)=1,H17,RIGHT(H17,LEN(H17)-1)),IF(TYPE(J17)=1,J17,RIGHT(J17,LEN(J17)-1)),IF(TYPE(L17)=1,L17,RIGHT(L17,LEN(L17)-1))))

!!!

I appreciate your help a lot.