Want to create Monthly Performance Report of my department

[sabdullahaziz]

Dear Friends,

First of all i am really great full to all of you who support people on this gr8 forum. I am working in a hospital as a Biomedical engineer and currently working on a project related to our department. I have very few knowledge of Excel and i need experts help in creating Monthly Performance Report according to the pattern in which our engineer's works on day to day basis. Below i will share all the required data and explain how i have to create report.

Data:

Total number of Engineers = 10
Total number of duty hours is = 8
Total number of duty hours in a month = 240
Total number of Complain = 30
Total number of Complain Complete = 9
Total number of Complain in progress = 9
Total number of Complain Complete Outsource = 6
Total number of Complain Progress Outsource = 6

Result:

"Total number of Complain = Auto calculate here
Total number of Complain Completed = Auto calculate here
Total number of Complain in Progress = Auto calculate here
Total number of Complain Complete Outsource = Auto calculate here
Total number of Complain Progress Outsource = Auto calculate here
% Total number of Complain Completed = Auto calculate here
% Total number of Complain in Progress = Auto calculate here
% Total number of Complain Complete Outsource = Auto calculate here
% Total number of Complain Progress Outsource = Auto calculate here
Engineer 1 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 2 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 3 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 4 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 5 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 6 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 7 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 8 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 9 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 10 Performance in % (according to time & complain resolve) Auto Calculate here
Monthly Performance in % of All Department = (Auto Calculate here)
Monthly Respond Time in % of Department = (Auto Calculate here)"

Note:
Note: We apply filter on Engineer's Name, Department or Status when we select any department, engineer or status the result will auto calculate only for the complain related to that department, if engineer then complain resolve by that engineer, if status then according to status of complain.

Screen Shot:
Imgur: The most awesome images on the Internet

You can download report File with format in which i highlighted each cell with red color where i want to put formula.

http://www.mediafire.com/file/82dzfqq46b3cocq/Format.xlsx

Many Thanks waiting for positive reply

 

[oldbrewer]

Dear Friends,

First of all i am really great full to all of you who support people on this gr8 forum. I am working in a hospital as a Biomedical engineer and currently working on a project related to our department. I have very few knowledge of Excel and i need experts help in creating Monthly Performance Report according to the pattern in which our engineer's works on day to day basis. Below i will share all the required data and explain how i have to create report.

Data:

Total number of Engineers = 10
Total number of duty hours is = 8
Total number of duty hours in a month = 240
Total number of Complain = 30
Total number of Complain Complete = 9
Total number of Complain in progress = 9
Total number of Complain Complete Outsource = 6
Total number of Complain Progress Outsource = 6

Result:

"Total number of Complain = Auto calculate here
Total number of Complain Completed = Auto calculate here
Total number of Complain in Progress = Auto calculate here
Total number of Complain Complete Outsource = Auto calculate here
Total number of Complain Progress Outsource = Auto calculate here
% Total number of Complain Completed = Auto calculate here
% Total number of Complain in Progress = Auto calculate here
% Total number of Complain Complete Outsource = Auto calculate here
% Total number of Complain Progress Outsource = Auto calculate here
Engineer 1 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 2 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 3 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 4 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 5 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 6 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 7 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 8 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 9 Performance in % (according to time & complain resolve) Auto Calculate here
Engineer 10 Performance in % (according to time & complain resolve) Auto Calculate here
Monthly Performance in % of All Department = (Auto Calculate here)
Monthly Respond Time in % of Department = (Auto Calculate here)"

Note:
Note: We apply filter on Engineer's Name, Department or Status when we select any department, engineer or status the result will auto calculate only for the complain related to that department, if engineer then complain resolve by that engineer, if status then according to status of complain.

Screen Shot:
Imgur: The most awesome images on the Internet

You can download report File with format in which i highlighted each cell with red color where i want to put formula.

http://www.mediafire.com/file/82dzfqq46b3cocq/Format.xlsx

Many Thanks waiting for positive reply

date of complaint	engineer	target closure date	complaint closed	status				today=	30/12/2016
01/12/2016	eng1	07/12/2016	05/12/2016	closed on time
02/12/2016	eng2	08/12/2016	05/12/2016	closed on time
03/12/2016	eng1	09/12/2016	08/12/2016	closed on time
04/12/2016	eng2	10/12/2016		open overdue
05/12/2016	eng1	11/12/2016	08/12/2016	closed on time
06/12/2016	eng2	12/12/2016	09/12/2016	closed on time
07/12/2016	eng1	13/12/2016	10/12/2016	closed on time
08/12/2016	eng2	14/12/2016	11/12/2016	closed on time
09/12/2016	eng1	15/12/2016		open overdue
10/12/2016	eng2	16/12/2016	19/12/2016	closed late
11/12/2016	eng1	17/12/2016		open overdue
12/12/2016	eng2	18/12/2016	15/12/2016	closed on time
13/12/2016	eng1	19/12/2016		open overdue
14/12/2016	eng2	20/12/2016	16/12/2016	closed on time
15/12/2016	eng1	21/12/2016	23/12/2016	closed late
16/12/2016	eng2	22/12/2016		open overdue
17/12/2016	eng1	23/12/2016	27/12/2016	closed late
18/12/2016	eng2	24/12/2016	23/12/2016	closed on time
19/12/2016	eng1	25/12/2016		open overdue
29/12/2016	eng2	04/01/2017		open on schedule
						closed on time	closed late	open on schedule	open overdue
					eng1	4	2	0	4
					eng2	6	1	1	2
			formula for 4 (eng 1 closed on time)
			=SUMPRODUCT(($B$2:$B$21=$F28)*($E$2:$E$21=G$27))

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>