Calculate average time excluding outliers

[Deepk]

Hi All,

I have the following time spent data. I want a VBA code that can give the average time spent, total time spent, and number of days in the last columns (variable) against each employee. The time entries less than 4:30 and greater than 13:00 hours should not be included during the average calculation.

I have done calculations for two rows to show the desired output.

Looking forward in anticipation!

Attendance_Jan 20221(AutoRecovered).xlsx
	C	D	E	F	G	H	I	J	K
4	Emp Name	Mon 03	Tue 04	Wed 05	Thu 06	Fri 07	Average	Total time spent	Number of days
5	ABC1	9:29	9:03	8:38	8:50	9:34	9:06	45:34:00	5
6	ABC2	8:30	9:29	9:01	7:36
7	ABC3	9:08	9:34	10:00	9:28	9:30
8	ABC4		9:04	9:27	9:00	8:21
9	ABC5	8:14	6:51			4:35
10	ABC6	9:20	6:49	10:14	9:04	9:00
11	ABC7	8:45	9:11	9:14	9:39	11:59
12	ABC8	9:11	8:58	9:10	9:33	8:51
13	ABC9	9:18	11:20	14:25	9:04	9:19
14	ABC10	9:46	3:15	9:12	4:25	5:24	8:07	24:22:00	3
15	ABC11	9:15	10:34	9:08	9:16	6:44
16	ABC12	9:19	8:45	9:29
17	ABC13	8:59	9:10	23:29	9:06	10:16
18	ABC14	13:34	12:12	3:15	10:02	8:50
19	ABC15	9:14	9:33	9:42	9:11	11:32
20	ABC16	9:03	9:10	9:58	3:15	8:55
21	ABC17		8:19	9:09	9:36	8:55
22	ABC18	9:10	9:26	9:38	9:19	9:30
Sheet9

 

[Jamie McMillan]

Hello Deepk,

This is not VBA. It is just formulas that will do what you want. I used numbers 1 - 5 in column D-H.

then in Cell I5 (Copy & PAste) =(SUMIF(D5:H5,">1")-SUMIF(D5:H5,">4"))/(COUNT(D5:H5)-(COUNTIF(D5:H5,">4")+COUNTIF(D5:H5,"<2"))) (Mean Average)
then in Cell J5 (Copy & PAste) =(SUMIF(D5:H5,">1")-SUMIF(D5:H5,">4")) (Total)
then in Cell J5 =J5/24 (Hours to Days)

* Obviously change the ">1" parts to fit your data.
Copy the formulas down to the other cells.

No code needed.

Hope this is good for you; with no VBA.

Jamie

 

[Deepk]

Hello Deepk,

This is not VBA. It is just formulas that will do what you want. I used numbers 1 - 5 in column D-H.

then in Cell I5 (Copy & PAste) =(SUMIF(D5:H5,">1")-SUMIF(D5:H5,">4"))/(COUNT(D5:H5)-(COUNTIF(D5:H5,">4")+COUNTIF(D5:H5,"<2"))) (Mean Average)
then in Cell J5 (Copy & PAste) =(SUMIF(D5:H5,">1")-SUMIF(D5:H5,">4")) (Total)
then in Cell J5 =J5/24 (Hours to Days)

* Obviously change the ">1" parts to fit your data.
Copy the formulas down to the other cells.

No code needed.

Hope this is good for you; with no VBA.

Jamie

hi Jamie,

Thank you for your reply.

Please note that the member who is going to use this sheet is not very friendly with excel and formulas. To simplify the process and we would need a vba code. Please help here. Thank you.

 

[Jamie McMillan]

Hello Deepk,

The code below is set for the first piece of data in D5: as per your image.

* Obviously change the ">7" "<4"parts to fit your data.

Sub TimeSpent()
Dim i As Integer, j As Integer
i = 1
j = 1
'Loops from row 5 to 15 (Change to the last row of your Data
For i = 5 To 15
' Change the <> values to suit 'Finds the total Hours
Cells(i, 10) = Cells(i, 4) + Cells(i, 5) + Cells(i, 6) + Cells(i, 7) + Cells(i, 8) - Application.WorksheetFunction.SumIf(Range("D5:H5").Offset(j - 1), ">7") - Application.WorksheetFunction.SumIf(Range("D5:H5").Offset(j - 1), "<4")
' Change the <> values to suit 'Finds the total Days
Cells(i, 11) = 5 - (Application.WorksheetFunction.CountIf(Range("D5:H5").Offset(j - 1), ">7") + Application.WorksheetFunction.CountIf(Range("D5:H5").Offset(j - 1), "<4"))
' Eliminates errors if zero hours - dividing by 0 ' Gives the Mean Average
If Cells(i, 11) > 0 Then
Cells(i, 9) = Cells(i, 10) / Cells(i, 11)
Else
Cells(i, 9).Value = 0
End If
j = j + 1
Next i
End Sub

Jamie