If function 10 condition

**Sam.K** · 10-20-2008, 08:21 AM

Hi,
I need some code for the if function that have more than 7 condition. so,in the words
1. IF GROUP1 ,TYPE1 ,point1<=10 ,point2>=5 Then E1="PASS"
2. IF GROUP1 ,TYPE2 ,point1<=10 ,point2>=5 Then E1= "PASS"
3. IF GROUP2 ,TYPE1 ,point1<=10 ,point2>=5 Then E1= "PASS"
4. IF GROUP2 ,TYPE2 ,point1<=10 ,point2>=5 Then E1= "PASS"
5. IF GROUP3 ,TYPE1 ,point1<=10 ,point2>=5 Then E1= "PASS"
6. IF GROUP3 ,TYPE2 ,point1<=10 ,point2>=5 Then E1= "PASS"
7. IF GROUP4 ,TYPE1 ,point1<=10 ,point2>=5 Then E1= "PASS"
8. IF GROUP4 ,TYPE2 ,point1<=10 ,point2>=5 Then E1= "PASS"
9. IF GROUP and TYPE and point1 and point2 ="" Then E1 =""
10. IF point1 >10 ,point2<5 OF ALL OF GROUP1-4 ,ALLOF ALL TYPE1-4 , E= "FAIL" or if not in that condition from condition 1-9 Then E1="fail"
by anyway i can do it.
thank

**xld** · 10-20-2008, 08:55 AM

=IF(AND(point1<=10,point2>=5, OR(GROUP1,GROUP2,GROUP3,GROUP4),OR(TYPE1,TYPE2)),"PASS","FAIL")

**xld** · 10-20-2008, 08:58 AM

re the book

=IF(AND(C3<=10,D3>=5, OR(A3="GROUP1",A3="GROUP2",A3="GROUP3",A3="GROUP4"),OR(B3="TYPE1",B3="TYPE2")),"PASS","FAIL")

**Sam.K** · 10-20-2008, 09:15 AM

Thank,for your help
i apply your code to my cell in e1
e1=IF(AND(C3<=10,D3>=5, OR(A3),OR(B3)),"PASS","FAIL")
but it be "#value" , can you tell me more how to do it
thank

**Sam.K** · 10-20-2008, 09:37 AM

Thank ,I test with the lastest code in cell e1.it work best ,but only one another condition .. if all of that cell are blank ,it still be "fail". can anyway to do it more , if all of it cell is blank ( no value), let it be blank not "fail"

**xld** · 10-20-2008, 11:22 AM

Here you are

=IF(COUNTIF(A3:D3,"")=4,"",IF(AND(C3<=10,D3>=5,OR(A3="GROUP1",A3="GROUP2",A3="GROUP3",A3="GROUP4"),OR(B3="TYPE1",B3="TYPE2")),"PASS","FAIL"))

**Sam.K** · 10-21-2008, 10:05 AM

Hi,thank
your last code work well. i do it for my work.
to day let me some question more for how to be the code as the nested if statement for my 10 condition which it difference to yesterday.
for all 4 group and 2 type have a differce value.let you see the detail condition in the attach file,please.

thank.

**xld** · 10-21-2008, 10:30 AM

Sorry, I am not clear on this one. Are you asking for a (slightly) different formula?

**Sam.K** · 10-21-2008, 08:17 PM

Hi, i need to solve with this ,with use of the nested if function.it is possible.
IF CONDITION
1. IF GROUP1 ,TYPE1 ,point1<=35 ,point2>=10 E1="PASS"
2. IF GROUP1 ,TYPE2 ,point1<=45 ,point2>=15 E1= "PASS"
3. IF GROUP2 ,TYPE1 ,point1<=60 ,point2>=20 E1= "PASS"
4. IF GROUP2 ,TYPE2 ,point1<=70,point2>=35 E1= "PASS"
5. IF GROUP3 ,TYPE1 ,point1<=80 ,point2>=40 E1= "PASS"
6. IF GROUP3 ,TYPE2 ,point1<=85 ,point2>=50 E1= "PASS"
7. IF GROUP4 ,TYPE1 ,point1<=90 ,point2>=55 E1= "PASS"
8. IF GROUP4 ,TYPE2 ,point1<=100 ,point2>=60 E1= "PASS"
9. IF GROUP ,TYPE ,point1 ,point2 ="" E =""
10. IF point1 ,point2 OF ALL OF GROUP1-4 ,ALLOF ALL TYPE1-2 IS NOT IN CONDITION OF1-8 , E= "FAIL"

**ratcat** · 11-01-2008, 01:25 AM

G'day Sam,

I was clean out my excel forum folder and saw this xl spreadsheet and say " O yeah I was going to solve this one"

Guess what I come up with a solution. I hope you like it.

See attached.

Cheers

**ratcat** · 11-01-2008, 03:00 AM

Hi all,

It appears that I didn't test the formula too well.

Please insert the correct formula in cell E1

Please Login or Register  to view this content.

Thanks and sorry for any headaches

Cheers

**Sam.K** · 11-01-2008, 03:47 AM

Hi,thanks so much ratcat
your code is very well ,it work.
but let me more some queution, for the other condition , if all of cell (group,type,point1,point2) are no value.can anyway for e1="" (blank) ,not it show n/a. how to apply the code.
Best regards.

**martindwilson** · 11-01-2008, 09:47 AM

Please Login or Register  to view this content.

**ratcat** · 11-01-2008, 07:28 PM

Originally Posted by martindwilson

Please Login or Register  to view this content.

G'day Sam,

Yeah, what Martin said. I'm glad you like the solution. Thanks for the thanks.

Cheers

@ martindwilson: Thanks for speed up the solution. Now I've got extra time to go and play some guitar now