School attendance calculator

Hi,

I've just joined today, so I'm sorry if i post this message in the wrong place or I'm not clear in my explanation.

I work in a school and i want to calculate the best possible attendance of a student based on their current attendance vs number of school days remaining.

There are 190 school days in a year.

For example, if a student is currently on 47% attendance and there are only 31 days left of the school academic year, what is their best possible attendance if they came to school every day.

So, I need a spreadsheet that can work out values based on all possible attendance and all possible number of school days remaining.

Thanks.

Something like attached file?

wow this is cool

How do you calculate the 54%, 63% and 21% for Student A, B and C respectively?

This information will be taken out of our school MIS.

But a simple calculation would be:

- 1st day of school - if present then 100% attendance

- 2nd day of school - if absent then it would be 50% attendance

- 3rd day of school - if present then attendance would be 33.33%

However there are many joiners and leavers. A student might join tomorrow and if they attend school tomorrow then their attendance would be 100%.

Again their daily attendance will be taken out of our school MIS.

Thanks.

1st day of school - if present then 100% attendance
2nd day of school - if absent then it would be 50% attendance
3rd day of school - if present then attendance would be 33.33% <- you mean 66.67%?

How can a student whose present attendance record is less than 100%, achieve 100% attendance by end of 190 school days?

With no. of school days passed in E2, remaining school days in F2 and how many more days attendance required to achieve in G2:
F3=($E$2*E3+$F$2)/190, copy down.
G3=IF($G$2*190-$E$2*E3>$F$2,"Impossible to achieve",$G$2*190-$E$2*E3), copy down.

That's Awesome!!!

Thank you so much for your time and effort. Much appreciated!

josephteh thank you for helping me out however after exploring the calculator, i think there are a few issues. Please can you help me again.

I have tried to clearly note comments down on the tab "josephteh2" of the attached spreadsheet.

Brief summary -

- table is not calculating properly as intended
- i have explained about attendance sessions rather than days due to how attendance % works in schools (My fault for not picking this up earlier, sorry).
- New tab created called "New table" - added in "sessions attended", "out of possible number of sessions", "number of sessions remaining in the school year"

Please can you take a look into this again and provide me with a solution.

Thank you.

Hi Ihussain, just got back from my CNY holiday.

Note 1: Student B
Days attended = 54 days * 76% = 41 days
To reach 80% attendance by end of school term = (41 days + 111 days) / 190 days = 80%

Note 2: Student D
Days attended = 54 days * 82% = 44 days
To maintain 80% attendance for the remaining school days = (44 days + 108 days) / 190 days = 80%

I believe you have to continue counting to the end of school term because the student may have achieved the 80% attendance now, but the % will drop if he/she stops going to school.

Note 3
You have to provide a date when a student joins.

For the last part, change 190 to 380 in formulas.

Hope you had a good holiday!

Note 1: Student B
Days attended = 54 days * 76% = 41 days
To reach 80% attendance by end of school term = (41 days + 111 days) / 190 days = 80%

That's great to have as a column for across the entire academic year but i would like a column to find out how many consecutive days the student will need to be in to hit their first 80%.

Note 2:

I understand, that makes sense.

Note 3:

The report that i get the student attendance information from does not have joined date. I could get the joined date from another report and merge them together, however this will be difficult for teachers, as i want to show them how to use it. Then they should take ownership of it themselves. So, i want it as simple as possible.

The report does show - "Number of possible sessions" and "Number of sessions attended".

I guess i could add a column to say "total number of sessions". This would be the total number of sessions currently possible in the year (regardless of when they joined, this is from the start of the academic year to the current day).
Can minus the above figure from the "number of sessions possible" to give a "joined at session" number.

I have added it into the sample spreadsheet on the tab "New table" .

Hope that makes some sort of sense.

Thanks.

Sorry, my mind is not working after a long holiday! I have requested for help from the experts here:
https://www.excelforum.com/the-water...ml#post5785039

Hopefully someone can assist you.

To create a school attendance calculator in Excel, you'll need to follow these steps:

Create a new Excel worksheet and name it "Attendance".

In the first row, create the headers for each column. These headers should include "Date", "Student Name", and "Attendance Status".

In the second row and below, enter the dates of the classes and the names of the students.

In the "Attendance Status" column, use a drop-down list to allow the user to select "Present", "Absent", or "Excused".

Create a formula that will count the number of "Present" and "Excused" entries for each student. You can use the COUNTIF function to do this. For example, the formula for counting the number of "Present" entries for Student 1 would be =COUNTIF(C2:C100,"Present").

Copy the formula for each student in the same column.

Create a pie chart that will display the attendance percentage for each student. To do this, select the data range for each student, go to the "Insert" tab, and click on the "Pie" chart.

Format the chart to your liking and label the slices with the students' names.

To calculate the overall attendance percentage for the class, you can use the AVERAGE function. For example, the formula to calculate the average attendance percentage for the class would be =AVERAGE(D2:D100).

Finally, you can add conditional formatting to highlight cells based on attendance status, for example, if a student is absent, the cell color can be red, and if the student is present, the cell color can be green.

This is a basic guide on how to create a school attendance calculator in Excel. You can customize and add more features to suit your needs.

Hi,

Thank you for your guide, however this isn't what i want to calculate.

I want to calculate the following things:

- what is the best possible attendance the student can achieve if they are in school every day from the next day onwards.
e.g. if a student is currently on 70% attendance today. What will be their best attendance if they attend school every day for the remaining number of school sessions

- How many number of consecutive sessions will the student have to attend to reach 75% attendance, 870% attendance, 90% attendance etc.
e.g. if a student is currently on 70% attendance, if they attend school every day onwards, their attendance will slowly go up. So, how many sessions will they need to consecutively attend before they reach 75%, then 80% and so on.

Parameters and variables to consider:

- 190 days in a school year however attendance is calculated by sessions not days.

- Each day has 2 sessions that count towards attendance figures (AM and PM)

- So, there are 380 sessions in a school year

- Attendance is calculated by the "number of possible sessions" divided by "number of sessions attended"

- Formulas have to account for the fact that students can join anytime in the school academic year. So for someone that joins last month, they won’t have 380 sessions. Their max sessions possible will be a lot less.

- For example - a student that joined school 2 days ago, if they miss 1 day of school (2 sessions attended out of 4 sessions) their attendance will be 50%. It won’t take them too long to get up to 80%.
However, a student who has been at the school from the beginning of the academic year. If they are absent half of that time, their attendance will also be 50%, but it will take them a lot longer to reach 80% attendance.


Please take a look at the attached spreadsheet.

It should have all the details needed to calculate my query.

I don't know if i will need a lookup table to calculate columns K, L and M. You will know better than me.

Thank you for your help.

Thank you for your response.

Can i please get clarification on a few things:

- Column K (How many more consecutive sessions attendance required to achieve 75% attendance) - Where the student is currently above 75% attendance, i assume the figure shown in Column K represents that the student will need to continue attending the following number of sessions to at least achieve 75% attendance.
e.g. Student C is currently on 84.62% attendance. The formula in column K has calculated 143 sessions.
So, the student has to consecutively attend 143 sessions for the remainder of the academic year to at least achieve 75% attendance.
Is that right?

- Column K - Student A - Currently on 70.76% attendance, will need to consecutively attend 125 sessions.
On the attached spreadsheet, if you go on the tab "Student A example", you can see the student needs to only attend 29 more consecutive sessions to achieve 75%

- Also, If the calculation in Column I is correct, then student A only has 159 more sessions left of the year. Column M says the student needs to attend 176 more sessions to achieve 90%. So, It's not possible for Student A to achieve 90% attendance..


I think your calculation takes into account the rest of the academic year, where as for column K to M, I want to know the very first instance where the student can achieve 75% attendance or 80% attendance etc.

I did the formula for column I - is it correct?
Or does Column I need to have a different header/formula on there to help with the calculation for column K to M.

I hope that makes sense.

Thanks.

The purpose of the calculator was really for students with low attendance figures.

Below 90%.

So a student with 70% attendance, we wanted to motivate them by saying, if you attend the next 30 sessions (or 15 days) you will get to 75%.
I know overall throughout the academic year they will need to attend a lot more to get an overall 75%. But we wanted to inform then when they will be on 75% attendance e.g. (Student A example tab)
We would of course inform them that the attendance will drop if the miss any sessions.
However, it's much more easier to get a child to attend school if we view the shorter term attendance rather than overall academic year.

Yes, exactly that!

Please could you put the formulas into the spreadsheet.

With an "if error" function or another formula for "not achievable" if the value goes over the possible sessions.

e.g. if number of possible sessions is 300 but it would take 350 sessions to reach 90%, then "not achievable".

Thank you so much.

Thank you and apologies.

It was because the formula didn't include the IF and MAX function that you just included.

i wasn't sure if it was helpful for me to include the initial formulas.

I really appreciate your help and knowledge. This will help the schools out massively.

Thank you for bearing with my annoyance and lack of knowledge on excel!