Result Analysis

[bbabu1]

Hai members,

I want to write a excel program for the following:

There are 8 semesters in engineering course. If a student is promoted with no arrears in 1st and 2nd semester to 2nd year (3rd semester) he is designated with WOBL (Without Backlog). If he get arrear in 1st and 2nd semester but he clears the arrears in the 3rd semester (excluding 3 semester subjects), he is designated as WBL (With Backlog). For this i have number of arrears in four columns (considering he is in second year now). The columns are A,B,C,D. A denotes no. of arrear in first semester. B denotes no. of standing arrear in 1st semester. C denotes no. of arrears in second semester. D denotes no. of standing arrears in second semester. If A,B,C,D is 0, then column E should say WOBL, else it should say WBL. Finally, i want to count number of students with WOBL, with WBL, with N/A. In short, the students are categorized into 3 types:

1. IF he has no history of arrears, he is WOBL
2. IF he has history of arrears and cleared before promoted to next year (odd semester) he is WBL.
3. IF he doesn't clears arrears but promoted to next year he is N/A (NOT APPLICABLE).

Model:

Row no. A B C D E
1 0 0 0 0 WOBL
2 3 0 1 0 WBL
3 0 0 1 0 WBL
4 2 1 2 1 NOT APPLICABLE (N/A)
5 4 0 0 0 WBL

I tried with the following formula, but i didn't get exact result:

=IF(AND(A1>0,B1=0,C1>0,D1=0),"WBL","WOBL","N/A")

Can any one help to write an Excel formula?

 

[AliGW]

Here you go:

=IF(AND(OR(A1>0,C1>0),B1=0,D1=0),"WBL",IF(SUM(A1:D1)=0,"WOBL","N/A"))

To count, you just need to use the COUNTIF function.

 

[Trustee]

Hi,

Hope this can help you:

=IF(AND(A1>0,C1>0),B1=0,D1=0),"WBL",IF(SUM(A1:D1)=0,"WOBL","N/A"))

 

[bbabu1]

Here you go:

=IF(AND(OR(A1>0,C1>0),B1=0,D1=0),"WBL",IF(SUM(A1:D1)=0,"WOBL","N/A"))

To count, you just need to use the COUNTIF function.

Excellent.It worked.Thanks a lot.

 

[AliGW]

Hi,

Hope this can help you:

=IF(AND(A1>0,C1>0),B1=0,D1=0),"WBL",IF(SUM(A1:D1)=0,"WOBL","N/A"))

Have you checked that your formula works? I think not ...

 

[AliGW]

Excellent.It worked.Thanks a lot.

You are very welcome!

 

[Trustee]

Okay, I couldn't try since I hadn't the document.

So the right formula is:

=IF(AND(OR(A1>0,C1>0),B1=0,D1=0),"WBL",IF(SUM(A1:D1)=0,"WOBL","N/A"))

Thank you !

 

[AliGW]

@Trustee - yes, correct - see post #2.

 

[bbabu1]

It is working for 4 columns. It is not working for 6 columns. I am getting #NAME? in the 7th column. Kindly help.