# Calculate Absence in a Time sheet based on working days per week!

1. ## Calculate Absence in a Time sheet based on working days per week!

The attached table shows 3 Group of employees, each group have different count of employees. And each group work on a different schedule, one on M-T-W, another from Mon-Fri, and another on Sat-Sun.

The table is to calculate how many employees were absent per week (Average and Percent), BUT based on the sessions (Workdays per week) in Column E
and how many were present.

Absence Average ?
Absence Percentage ?
Present ?

The numbers in columns F to L is the number of employees that were absent on that day of the week.

It could be so simple but with my knowledge in Excel I couldn't figure it out with a formula.

Thanks  Register To Reply

2. ## Re: Calculate Absence in a Time sheet based on working days per week!

Excel 2016 (Windows) 32 bit
M
N
2
Absence Average
Absence Percentage
3
1.333333333
13%
4
1.2
20%
5
3.5
35%
 Sheet: Sheet1

Excel 2016 (Windows) 32 bit
M
N
2
Absence Average
Absence Percentage
3
=AVERAGE(Table1[@[Mon]:[Wed]])
=SUM(Table1[@[Mon]:[Wed]])/[@[Employees total]]
4
=AVERAGE(Table1[@[Mon]:[Fri]])
=SUM(Table1[@[Mon]:[Fri]])/[@[Employees total]]
5
=AVERAGE(Table1[@[Sat]:[Sun]])
=SUM(Table1[@[Sat]:[Sun]])/[@[Employees total]]
 Sheet: Sheet1

I did not complete the last column because I don't understand your needs. Please explain your criteria/logic for this item as it could vary depending upon your needs.  Register To Reply

3. ## Re: Calculate Absence in a Time sheet based on working days per week!

Thanks a lot alansidman!

That's great! but is there a way to apply the same formula for all rows from [Mon] to [Sun] but to exclude empty cells?

So that I do not need to change the formula for each row?

Thanks  Register To Reply

4. ## Re: Calculate Absence in a Time sheet based on working days per week!

Change the schedule from M-T-W, M-T-W-T-F and S-S TO [Mon]:[Wed], [Mon]:[Fri] and [Sat]:[Sun] respectively.

Try the following formulas:
M3:=AVERAGE(INDIRECT("Table1[@"&[@Schedule]&"]"))
N3:=SUM(INDIRECT("Table1[@"&[@Schedule]&"]"))/([@[Employees total]]*COUNT(INDIRECT("Table1[@"&[@Schedule]&"]")))
O3:=100%-[@[Absence Percentage]]  Register To Reply

5. ## Re: Calculate Absence in a Time sheet based on working days per week!

Thank you all, it works.  Register To Reply

6. ## Re: Calculate Absence in a Time sheet based on working days per week!

@josephteh

im learning as i see some of the interesting methods used, and your solution here interests me.

i would like to ask, how would someone enter the Schedule if the person/group worked specific days with or without a range of days?
for example (Mon, Wed, Fri), or (Mon to Thu and Sat)  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 