Calculating attendance without using regular attendance formula

[DarrenK]

Good afternoon,

I'm stumped. My department is building a stack rank and I was asked to do a mock up. Below is what I created. ColA through ColM. The blanks are for days the agent was not scheduled so I did not want it to count for/against anyone so I left it blank. It looks like the following and for all intents and purposes, appeared to do the job. I would just simply copy/paste the values of column M into another worksheet.

[TABLE]
[TR]
[TD]Agent Name[/TD]
[TD]Mon 5/2[/TD]
[TD]Tue 5/3[/TD]
[TD]Wed 5/4[/TD]
[TD]Thu 5/5[/TD]
[TD]Fri 5/6[/TD]
[TD]Mon 5/9[/TD]
[TD]Tue 5/10[/TD]
[TD][/TD]

[TD][LEFT]PRESENT[/LEFT][/TD]
[TD][LEFT]ABSENT[/LEFT][/TD]
[TD][LEFT]Total WorkDays[/LEFT][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][LEFT]John[/LEFT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][/TD]

[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][/TD]

[TD][RIGHT]5[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]6[/RIGHT][/TD]
[TD][RIGHT]83%[/RIGHT][/TD]
[/TR]
[TR]
[TD][LEFT]Shirley[/LEFT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][/TD]

[TD][RIGHT]6[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]86%[/RIGHT][/TD]
[/TR]
[TR]
[TD][LEFT]Angel[/LEFT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][/TD]

[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]100%[/RIGHT][/TD]
[/TR]
[TR]
[TD][LEFT]Rebecca[/LEFT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][/TD]

[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]100%[/RIGHT][/TD]
[/TR]
[TR]
[TD][LEFT]Daniel[/LEFT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][RIGHT]1[/RIGHT][/TD]
[TD][/TD]

[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]0[/RIGHT][/TD]
[TD][RIGHT]7[/RIGHT][/TD]
[TD][RIGHT]100%[/RIGHT][/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]
[/TR]
[TR]
[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[TD][LEFT]j2:j6 =COUNTIF(B2:H2,"1")[/LEFT][/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][LEFT]k2:k6 =COUNTIF(B2:H2,"0")[/LEFT][/TD]
[TD][/TD]

[TD][/TD]

[TD][/TD]
[/TR]
[/TABLE]

However, my supervisor came back with the following: "For attendance, instead of assigning a percentage per day x 20 working days... how can we calculate this type of thing:

SCORE​	Criteria​
Exceeds Expectations​	No attendance instances within 6 mo.​
Nearly Exceeds Expectations​	1-2 attendance instances within 6 mo.​
Meets Expectations​	3-4 attendance instances within 6 mo. (nothing / Verbal)​
Nearly meets Expectations​	4-5 attendance instances within 6 mo. (Verbal / Written)​
Does not meet Expectations​	5+ attendance instances within 6 mo. (Written or above)​

That's all I have at this moment. Other than the fact that attendance instances work on a rolling calendar year. Without seeing what the HR attendance spreadsheet looks like, is there any advice on how I should have this formatted in order to provide the results my management team is looking for? If we can avoid macros, that would be stupendous.

 

[DarrenK]

A

B

C

D

E

Fri 5/6

G

H

I

J

K

L

M

Agent Name

Mon 5/2

Tue 5/3

Wed 5/4

Thu 5/5

Fri 5/6

Mon 5/9

Tue 5/10

PRESENT​

ABSENT​

Total WorkDays​

John​

1​

1​

1​

1​

0​

1​

5​

1​

6​

83%​

Shirley​

1​

1​

1​

0​

1​

1​

1​

6​

1​

7​

86%​

Angel​

1​

1​

1​

1​

1​

1​

1​

7​

0​

7​

100%​

Rebecca​

1​

1​

1​

1​

1​

1​

1​

7​

0​

7​

100%​

Daniel​

1​

1​

1​

1​

1​

1​

1​

7​

0​

7​

100%​

j2:j6 =COUNTIF(B2:H2,"1")​

k2:k6 =COUNTIF(B2:H2,"0")​

 

[DarrenK]

Sorry for the incorrect use of the VBA option.

 

[DarrenK]

Agent Name	Half Points	Full Points	D col =B2*0.5	E col =C2	F col =D2+E2
John	2​	3​	1​	3​	4​
Shirley	1​	2​	0.5​	2​	2.5​
Angel	1​	1​	0.5​	1​	1.5​
Rebecca	1​	2​	0.5​	2​	2.5​
Daniel	0​	2​	0​	2​	2​

I built the above so once I'm provided the attendance occurrence values, I can calculate the agent's total points. Then I'm thinking using a series of IF statements (which I apologize I am not comfortable with) I might then be able to determine where each agent stands. Does that make sense?