# Want to create Monthly Performance Report of my department

#### sabdullahaziz

##### New Member
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.

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

Many Thanks waiting for positive reply

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### oldbrewer

##### Well-known Member
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.

Code:
``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>

Replies
2
Views
294
Replies
1
Views
712
Replies
3
Views
540
Replies
4
Views
283
Replies
2
Views
887

1,191,029
Messages
5,984,228
Members
439,879
Latest member
KingGoulash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back