Thread: If statement hwelp

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

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


"Earl" wrote:

> 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
>
>

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



"bpeltzer" wrote:

> 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
>
>
> "Earl" wrote:
>
> > 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
> >
> >