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(A10.002<0.015,"Below Action Level of 0.015",IF(A10.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 |
If statement hwelp
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(A10.002<0.015,"Below Action Level of 0.015",IF(A10.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 |
If statement hwelp
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(A10.002<0.015,"Below Action Level of 0.015",IF(A10.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 |
All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com