Looking for a formula for two scenarios, please help

[central1010]

Hi everyone,

I am a staff of a small private training establishment, am producing an attendance sheet by using excel.

I have 2 scenarios which I have been looking for formulas to sort them out, here is the story:

In the attendance sheet, we have some markings for being late, half-day, absent... etc. For each data, I set a value for them in order to calculate the student's attendance percentage.

For being Late (L) = 1, Half-day (HD) = 0.5, Absent (A) = 0, and our school has a policy of the attendance.

2 times of L = 1 time of A; 2 times of HD = 1 time of A

For instance, the student was late twice and being on time for the rest of last week, the attendance of last week will show 4 out of 5 days, because we marked 1 of the 2 Ls, so there will be 1L 1A 3P (Present). For the 2HD = 1A works the same.

Sorry if I confused you all, but please help me with this complicated scenarios. I have been struggling with this for a week.

It will be appreciated if you could help me with this.

Thank you so much!

 

[bosco_yip]

Or,

Try, F2 formula copy down :

=COUNTA(A2:E2)-INT(COUNTIF(A2:E2,"L")/2)-(MOD(COUNTIF(A2:E2,"HD"),2)+INT((COUNTIF(A2:E2,"HD")-2)/2)*3+3)/2&"/"&COUNTA(A2:E2)

Regards

 

[central1010]

Thank you so much, Peter_SSs!

 

[central1010]

Thank you so much, bosco_yip

 

[central1010]

Happy new year! Thank you everyone for helping!

 

[central1010]

Hi everyone! Happy new year!

Thank you so much for anyone who helped me.

However, I have another scenario now, which is based on my previous two.

After I digested the formulas which are from Peter_SSs and bosco_yip (I appreciated your help so much!), I tried to make a small change and see if the formulas could solve my question. But it didn't work well, therefore I am here to ask for you help again, please.

Here is the previous table,

Attendance

	A	B	C	D	E	F
Mon	Tue	Wed	Thu	Fri
L	L	P	P	P
HD	HD	P	P	P
L	L	L	P	P
HD	HD	HD	HD	P

<colgroup><col style="width: 30px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 82px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: right"]Attendance[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]3.5[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]2

[/TD]

</tbody>

And the latest scenarios are:

2L = 1A (Solved)
2HD = 1A (Solved)
1L + 1HD = 1A (This is a new one)

<table border="1" cellspacing="0" cellpadding="0" collapse;="" border-spacing:="" 0px;="" font-size:="" 8pt;="" margin-bottom:="" 1em;="" font-family:="" arial,="" arial;="" padding-left:="" 2pt;="" padding-right:="" 2pt;"="" width=""><tbody>[TR="bgcolor: #CACACA"]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD="align: right"]Attendance[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD]P[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD]HD[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody></table>
This time, I would like to apply the theories into the whole attendance sheet, instead of calculating the attendances separately by rows.

For example, row 2 has a L and row 3 has a HD. The attendance of row 2 will still show 5 out of 5, but due to 1L + 1HD = 1A, the attendance of row 3 will show 3.5/5.

The combined result will always show at the following row.

The formula I am using at the moment is =COUNTIF(A2:E2,"P")+0.5*(INT(COUNTIF(A2:E2,"HD")/2)+MOD(COUNTIF(A2:E2,"HD"),2))+INT(COUNTIF(A2:E2,"L")/2)+MOD(COUNTIF(A2:E2,"L"),2)

I was changing the range, but it didnt work as I want. Therefore, I am here to ask for help again.

It will be appreciated if you could help me with this.

 

[central1010]

Long quote removed

Sorry, the table is supposed to be:

<table class="wysiwyg_dashes" style="width: 500px;" width="500"><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]Attendance[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]P[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]HD[/TD]
[TD]HD[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]P[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]L[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]5[/TD]
[/TR]
</tbody></table>

 

[Peter_SSs]

I removed the quote of your long incorrect table as it added nothing and makes the thread harder to read/navigate.
I suggest that you have a look at my signature block below for better ways to post small screen shots.

 

[central1010]

I removed the quote of your long incorrect table as it added nothing and makes the thread harder to read/navigate.
I suggest that you have a look at my signature block below for better ways to post small screen shots.

Thank you so much, Peter.