Result Analysis

bbabu1

New Member
Joined
Nov 26, 2016
Messages
8
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0

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"))
 
Upvote 0
ADVERTISEMENT

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 ... ;)
 
Upvote 0
ADVERTISEMENT
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 !
 
Upvote 0
It is working for 4 columns. It is not working for 6 columns. I am getting #NAME? in the 7th column. Kindly help.
 
Upvote 0

Forum statistics

Threads
1,196,409
Messages
6,015,105
Members
441,870
Latest member
kojack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top