Employees performance chart

Quintrala

New Member
Joined
Oct 6, 2017
Messages
3
Good morning. It's my first time seeking help here. I have an Excel spreadsheet which has attendance reports for all employees working in the company. I want to create a pie chart which shows the percentage of good attendance, late arrivals, leaving early and absences per person each week and another for the same data per all employees each week. I have created totals for each value for each employee per each day (H2 to J7 in the image). As well as daily totals for all employee (B10 to G13 in image).

The spreadsheet looks as below.

5w3Af.png

I want to know how to add the info into a pie chart since I have been following regular pie chart creation process and is not creating what I need but only adding one of the values instead of all.

Eventually I want to use the pie charts to create a per month and yearly report as well.

Can someone help me?

Thanks in advance for the help!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
why is total week LA for monday = 6

and is pie chart for each day? as you cant have mon to fri with 4 scores for each on a pie chart
 
Upvote 0
why is total week LA for monday = 6
I did not add up real totals for the example in the picture. Just entered random numbers to create an image alike what I have since I need to protect employees names privacy.-

"and is pie chart for each day?"

First pie chart is mostly looking to compare employees on how they affect. An employee who is late every day will have a higher area of the pie while an employee with 0 lateness will not be even there. This one should use columns on far right area.

Second pie chart looks for how much each category affects the week. This would be seen trough the amount of late personnel per day. This one should use rows at the bottom of the sheet.

"as you cant have mon to fri with 4 scores for each on a pie chart."

Could not understand this part.
 
Upvote 0
[TABLE="width: 978"]
<colgroup><col span="8"><col><col><col span="5"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]mon[/TD]
[TD]tue[/TD]
[TD]wed[/TD]
[TD]thu[/TD]
[TD]fri[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]emp1[/TD]
[TD] [/TD]
[TD]LA[/TD]
[TD]LA[/TD]
[TD] [/TD]
[TD]LA[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD="align: right"]0.0000002[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]emp2[/TD]
[TD] [/TD]
[TD]LA[/TD]
[TD]LA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[TD="align: right"]0.0000003[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]emp3[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD="align: right"]0.0000004[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="colspan: 4"]this last column is number of LA's[/TD]
[/TR]
[TR]
[TD]emp4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]LA[/TD]
[TD]LA[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD="align: right"]0.0000005[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="colspan: 3"]plus row number times .000001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]emp5[/TD]
[TD]LA[/TD]
[TD] [/TD]
[TD]LA[/TD]
[TD]LA[/TD]
[TD]LA[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD="align: right"]0.0000006[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="colspan: 3"](to sort out tie breaks)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]emp6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0[/TD]
[TD="align: right"]0.0000007[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]PUT THE LAST COLUMN IN DESCENDING ORDER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD]emp5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]emp1[/TD]
[TD]3[/TD]
[TD="colspan: 4"]and use offset match to attach names[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD]emp4[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD]emp2[/TD]
[TD]2[/TD]
[TD="colspan: 4"]copy the numbers to right of name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD]emp6[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD]emp3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]this makes a nice pie chart with emp5 and emp6 not actually showing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]but indicated as having zero value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I am so sorry. I'm a totally new user so I still don't get this since I don't even know what offset match is. Like I really need the step by step. I'm so sorry. :(
 
Upvote 0
No problem. Offset match is easy, start here, go down so many cells, go across so many cells. So offset(A1,7,5) returns the value in E8. Match just looks for a match in a row or column, So match(66,C1:H1,0) finds 66, say it is in E1 then the formula returns 2. So we can use match within offset.

offset(A3, match("red",A4:A10,0),match(66,C1:H1,0)) finds red then tracks across from C1 to H1 until it finds red. If 66 is in G1 and red is in A7 it goes to A7 and then across to column 7 ie G7.

I will post the formula I used later today.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top