Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problems with IF, AND + OR statment
I have a range with data looking like this:
C D E R6 max -2lgo 5,8 R7 max 5ppm 5,0 R8 min 5ppm 5,5 R9 max -2lgo 5,8 R10 max MK1 3,5 R = Row number I want to "extract" the values from the E column to the G column if the following coditions are met. If C6 = max and D6 = -2 lgo or D6 = 5ppm I want the value of 5,8 in cell G6. If any of these conditions are false I want G6 to show 0. I have tried the following formulas: =IF(AND(OR(C6="max";D6="-2 lgo";D6="5ppm"));E6;0) This formula gives the result 5,5 in G8 insted of 0 and 3,5 in G10 insted of 0. The other values are ok. I have also tried =IF(AND(C6="max";IF(OR(D6="-2 lgo";D6="5pp");E6));0) This formula puts "FALSE" in G8 and G10 and 0 in G6, G7 and G9. Would be gratefull for any help. Using Excel 2002 |
#2
|
|||
|
|||
Try this, Alf.
=IF(AND(C6="max",OR(D6="-2lgo",D6="5ppm")),5.8,0) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "alf bryn" wrote in message ... I have a range with data looking like this: C D E R6 max -2lgo 5,8 R7 max 5ppm 5,0 R8 min 5ppm 5,5 R9 max -2lgo 5,8 R10 max MK1 3,5 R = Row number I want to "extract" the values from the E column to the G column if the following coditions are met. If C6 = max and D6 = -2 lgo or D6 = 5ppm I want the value of 5,8 in cell G6. If any of these conditions are false I want G6 to show 0. I have tried the following formulas: =IF(AND(OR(C6="max";D6="-2 lgo";D6="5ppm"));E6;0) This formula gives the result 5,5 in G8 insted of 0 and 3,5 in G10 insted of 0. The other values are ok. I have also tried =IF(AND(C6="max";IF(OR(D6="-2 lgo";D6="5pp");E6));0) This formula puts "FALSE" in G8 and G10 and 0 in G6, G7 and G9. Would be gratefull for any help. Using Excel 2002 |
#3
|
|||
|
|||
Thanks Anne, you solved my problem!!!
"Anne Troy" wrote in message news:ae8ff$42bd4207$97c5108d$19571@allthenewsgroup s.com... Try this, Alf. =IF(AND(C6="max",OR(D6="-2lgo",D6="5ppm")),5.8,0) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "alf bryn" wrote in message ... I have a range with data looking like this: C D E R6 max -2lgo 5,8 R7 max 5ppm 5,0 R8 min 5ppm 5,5 R9 max -2lgo 5,8 R10 max MK1 3,5 R = Row number I want to "extract" the values from the E column to the G column if the following coditions are met. If C6 = max and D6 = -2 lgo or D6 = 5ppm I want the value of 5,8 in cell G6. If any of these conditions are false I want G6 to show 0. I have tried the following formulas: =IF(AND(OR(C6="max";D6="-2 lgo";D6="5ppm"));E6;0) This formula gives the result 5,5 in G8 insted of 0 and 3,5 in G10 insted of 0. The other values are ok. I have also tried =IF(AND(C6="max";IF(OR(D6="-2 lgo";D6="5pp");E6));0) This formula puts "FALSE" in G8 and G10 and 0 in G6, G7 and G9. Would be gratefull for any help. Using Excel 2002 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statment using Criteria | Excel Discussion (Misc queries) | |||
How do I use 'And' or "Or" in an If statment | Excel Worksheet Functions | |||
Multiple IF THEN ELSE statment | Excel Worksheet Functions | |||
How do I keep the format of a cell that an if statment refers to? | Excel Discussion (Misc queries) | |||
If statment | Excel Worksheet Functions |