If statement hwelp

[Guest]

My data:
A B
1 <.002 0.019
2 Sample was non-compliant
3 0.00323 0.156
4 0.002 0.012
5 0.00491 0.030
6 0.119 0.096

Formula:
=IF(A1="<.002","Less than 0.002 mgl, None Detected",IF(A1>0.002<0.015,"Below
Action Level of 0.015",IF(A1>0.015,"This is above the action limit of
0.015")))

Problem:
Using cell A2 returns "This is above the action limit of 0.015" and I want
'Sample was non-compliant'

I would like the contents of the cell displayed in the statement result for
all cells/conditions

Using cell A3 returns "FALSE" and I want "Below Action Level of 0.015"

TIA

 

[Guest]

For starters, if you want to require multiple conditions be met (>.002,<0.15)
you need to use AND: if(and(A1>.002,A1<.015),true_result,false_result)
Next, the final if in your formula really isn't needed; it's just the 'else'
part of the second if.
So I think the formula would be =if(a1="<.002","Less then
..002...",if(and(a1>.002,a1<.015),"Below action level..","Above action
limit..."))
BUT, a big remaining issue, I suspect, is the difference between numbers and
text strings that look like numbers. What happens, for instance, if you
select the data in column A and apply the comma style? If the appearance
doesn't change, you've got text and your comparison is using numbers. If
that's the case, enter the number 0 in some random cell and copy if (ctrl+c),
then select your column of data and Edit > Paste Special, select Values and
Add, then click OK. That will force conversion of numeric text to numbers.
--Bruce

 

[Guest]

The formula worked great.

The text to number conversion had no effect, is there another way?

Also, is there a way to embed the column A data in the formula to be
displayed within the result...'at 0.00323 mg/l this is below the action limit
of 0.015 mg/l'.

TIA