Generate Students Report Monthly Classwise

[kashifshahzad]

Generate Students Report Monthly Classwise.


I have file including following worksheets
1.Settings
2.Attendance Register (It contains data of students name, class,status, date)
3.Class Attendance Report (user has choice tochoose any class from list, and the relevant data of that particular class comedown in sheet like rollno, name, month and attendance percentage
Problem. In the sheet named ClassAttendance Report when user choose class name from the list the rollno, studentname data is coming right, but the attendance percentage which I calculatedgives wrong value, I want the exact attendance value of particular rollno which user choose from the list.
See the uploaded file for reference from below link.

https://www.dropbox.com/s/j5ljsdvfb2...%202.xlsx?dl=0


Thanks in advance

[JohnTopley]

Please post your file to the forum as many respondents do not want to visit file-hosting sites.

[kashifshahzad]

I have attached file here. please check. Thanks.

[JohnTopley]

I have changed some of the formulae to make them generic.

in B33 (Rol lno)

=IFERROR(INDEX(RollNo,SMALL(IF(Class=$C$14,ROW(RollNo)-ROW($B$10)+1,""),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter

in C33

=IFERROR(VLOOKUP($B33,'Attendance Register'!$B$10:$C$1050,2,0),"")

in D33

=IF($C33<>"",SUMPRODUCT(('Attendance Register'!$H$9:$NG$9<=MIN(TODAY(),D$24))*('Attendance Register'!$H$9:$NG$9>=EOMONTH(D$24,-1))*('Attendance Register'!$C$10:$C$2000='Class Attendance Report'!$C33)*(ISNUMBER(MATCH('Attendance Register'!$H$10:$NG$2000,{"PRESENT","LEAVE"},0))))/$D$25,"")

Copied across to E and F and down columns D, E, F

[JohnTopley]

See the attached:

[kashifshahzad]

Great....its worked... Thank you very much.

[kashifshahzad]

Here I found one problem, when you changed formula now Its giving all students (even their status is dropped) infact I want only the data of those students which status is active in Attendance Register sheet F10.

[JohnTopley]

Thank you for the feedback.

If your question has been answered could you please mark the thread as solved ("Thread Tools" at top of first post").

[kashifshahzad]

Here I found one problem, when you changed formula now Its giving all students (even their status is dropped) infact I want only the data of those students which status is active in Attendance Register sheet F10.

[JohnTopley]

Change "Roll No" formula to only select "Active" students

=IFERROR(INDEX(RollNo,SMALL(IF((Class=$C$14)*(Status="Active"),ROW(RollNo)-ROW($B$10)+1,""),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter

You need to change the "Overall" calculation to be similar to the Monthly attendance calculation.

[kashifshahzad]

yes infact I have major problem in calculate the monthly attendance calculation of active students. I tried many logic couldn't success. if you can guide little more. thanks in advance.

[JohnTopley]

I have major problem in calculate the monthly attendance calculation of active students

The figures provided looked OK to me (I didn't check every student).


Post a file showing expected results: in the file you posted all students are "Active" - what exactly is (are) the problem(s)??

Explain any calculations needed.

[JohnTopley]

... one error ...


{"PRESENT","LEAVE"},0))))/$D$25,""),"")

should be ..

{"PRESENT","LEAVE"},0))))/D$25,""),"") (no $ in front of the D)

[JohnTopley]

Formula for attendance should be ...

=IFERROR(IF($C33<>"",SUMPRODUCT(('Attendance Register'!$H$9:$NG$9<=MIN(TODAY(),D$24))*('Attendance Register'!$H$9:$NG$9>EOMONTH(D$24,-1))*('Attendance Register'!$C$10:$C$2000='Class Attendance Report'!$C33)*(ISNUMBER(MATCH('Attendance Register'!$H$10:$NG$2000,{"PRESENT","LEAVE"},0))))/D$25,""),"")


my error ( I had ">=" rather than just ">")

[kashifshahzad]

This formula seems okay. but it has one problem if there are students with similar names it gives wrong data. so I changed your formula little bit I put $B33 which is RollNo Field and roll no is unique instead of $C33 which is name field which may be same. so I used following formula.
=IFERROR(IF($B33<>"",SUMPRODUCT(('Attendance Register'!$H$9:$NG$9<=MIN(TODAY(),D$24))*('Attendance Register'!$H$9:$NG$9>EOMONTH(D$24,-1))*('Attendance Register'!$C$10:$C$2000='Class Attendance Report'!$B33)*(ISNUMBER(MATCH('Attendance Register'!$H$10:$NG$2000,{"PRESENT"},0))))/D$25,""),"")

by ctril shift enter

but it gives 0% for all students. unable to detect the error. please note I need to display the attendance result only for those students whose status is active. dropped students list should not come. sorry if I am bothering you.

[JohnTopley]

You need to change the range ... from C to B

=IFERROR(IF($B33<>"",SUMPRODUCT(('Attendance Register'!$H$9:$NG$9<=MIN(TODAY(),D$24))*('Attendance Register'!$H$9:$NG$9>EOMONTH(D$24,-1))*('Attendance Register'!$B$10:$B$2000='Class Attendance Report'!$B33)*(ISNUMBER(MATCH('Attendance Register'!$H$10:$NG$2000,{"PRESENT"},0))))/D$25,""),"")

[kashifshahzad]

yes i already changed range from C to B
but the result is caclulation is 0% to all. which is totally wrong.

[JohnTopley]

It calculates OK in the file you posted.

[JohnTopley]

See the attached ....

[kashifshahzad]

It seems okay now. but only problem is that it takes too long to calculate even on Core I-7 Processor with 8GB RAM.

[JohnTopley]

Yes ... calculation time is a problem.

Your original formulas uses COUNTIF which is much faster but doesn't have the flexibility of SUMPRODUCT (2 dimensional arrays)..

And your originals are "hard-coded" so if you change the class all the formulas need to change (range references).

I will re-look at this and see if I can find a quicker but generic way of doing the calculations.

[kashifshahzad]

Thanks you very much. if your found some quicker but generic please do share with me

[JohnTopley]

Try this

in D33

=IF($C33<>"",SUM(COUNTIFS('Attendance Register'!$H$9:$NG$9,"<="&MIN(TODAY(),D$24),'Attendance Register'!$H$9:$NG$9,">"&EOMONTH(D$24,-1),OFFSET('Attendance Register'!$B$10,MATCH('Class Attendance Report'!$B33,RollNo,0)-1,6,1,364),{"PRESENT","LEAVE"}))/D$25,"")


Copy across to E & F

then down

[JohnTopley]

Alternative offset ...


=IF($C33<>"",SUM(COUNTIFS('Attendance Register'!$H$9:$NG$9,"<="&MIN(TODAY(),D$24),'Attendance Register'!$H$9:$NG$9,">"&EOMONTH(D$24,-1),OFFSET('Attendance Register'!$H$10,MATCH('Class Attendance Report'!$B33,RollNo,0)-1,0,1,364),{"PRESENT","LEAVE"}))/D$25,"")

[kashifshahzad]

It's bi faster than previous used formula. Thanks once again

[JohnTopley]

Considerable faster than SUMPRODUCT!!!

Anyway, if your problem had been solved, can you mark thread as such ("Thread Tools" at top of first post).