Greetings to you.
Please find attached the spreadsheet with 3 sheets.
Sheet one is 1st score book, sheet two is 1st analysis while sheet three is 1st Broadsheet
The problem I am trying to solve is on both sheet two and three.
Now on sheet 2 which is 1st analysis is am trying to populate all the columns and the values to use are all in sheet 1 which is the 1st score book
REGISTERED: to populate this, we will use all students registered for each class i.e (Jss 1, Jss 2, Jss 3) regardless of whether the student took the exam or not. Male, Female and the addition of both gives us the total for that class

PRESENT: Here, we will only capture the students that participated in atleast one exam or more (Male, Female and addition of both gives the total)

ABSENT: Here, we will capture only the students who were registered but did not take any exam (Male, Female and addition gives the total)

No. of student that passed with 50% average including ENGLISH & MATHS: To calculate this, we will use 3 criteria from the sheet one which are; the student must have 50% and above in English, Maths and the total percentage in column “BS” must also be 50% and above.

OTHERS: these are all other student that does not meet the previous criteria.

% PASS: the formula for this is TOTAL of No. of student that passed with 50% average including ENGLISH & MATHS divided by Number PRESENT Multiplied by 100

Please note: the addition of Number PRESENT, Number ABSENT, No. of student that passed with 50% average including ENGLISH & MATHS and OTHERS MUST be equal to Number REGISTERED



When we are done with this 1st part, I will explain the 2nd part so we don't get overwhelmed.

Thank you for assistance.

Attached Files

msoff.xlsx (53.8 KB, 3 views)

Can you show some expected result according to your raw datas?
I am not clear about your need.

See attached attempt
I don't know which are the previous criteria so I've left that out.

At first sight, that seems impossible (unless everyone fails)!

Attached Files

msofficeforums51696msoff.xlsx (54.2 KB, 0 views)