• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need help creating a dynamic formula to tracks the employees attendance points

Y

Yitzhackm

Member
I need help creating a dynamic formula . I have a book to tracks the employees attendance occurrences.



The occurrences tab tracks if the employee was either tardy, left early, was absent or NC/NS. Where ABS and NCNS has a value of one and TAR and EO has a value of 0.5 if any of those equals less than 240 but greater 241 equals one full point.

The total points will be tracked on the roster tab column H. The point fall off after 180 days from the day of the occurrence. My problem is that my formula in column H does not reads that some points “Rolled Off” as you can see in my example. Roster Tab shows that Felipe Martinez has occurred a total of 3.5 points however two out those 3.5 rolled off but my formula is not tracking that. At the same time column I needs to track if

· 5 and 5.5 points = Verbal

· 6 and 6.5 points = Written

· 7 and 7.5 = Final

· 8 Termination



Hope makes sense. Thank is advance.

 

Attachments

  • MAHC_Occurrence_Tracker_MASTER test 10 25 2013.xlsx
    120.4 KB · Views: 36
Hi, Yitzhackm!

Yitzhackm said:
The occurrences tab tracks if the employee was either tardy, left early, was absent or NC/NS. Where ABS and NCNS has a value of one and TAR and EO has a value of 0.5 if any of those equals less than 240 but greater 241 equals one full point.
Click to expand...
Ok with the .5/1 but don't understand <=240 or >241, a number can't fill both conditions.

Despite of this, try changing formula in H2 to:
=SUMAR.SI.CONJUNTO(Occurrences!F:F;Occurrences!A:A;A2;Occurrences!I:I;"<"&90) -----> in english: =SUMIFS(Occurrences!F:F,Occurrences!A:A,A2,Occurrences!I:I,"<"&90)
Your formula was taking rolled off entries since you didn't include any condition regarding that.

BTW, in worksheet Roster is it missing a column "English", isn't it? Or just an "X" in column F is only an attribute not discriminative?

For column I results, do you think you could be able to build a 2 column table with point ranges in 1st column and description in 2nd one, and place a search formula on it? Si no puede, por favor avise.

Regards!

PS: If you can't, please advise (for the community).
 
SirJB7 said:
Hi, Yitzhackm!


Ok with the .5/1 but don't understand <=240 or >241, a number can't fill both conditions.

Despite of this, try changing formula in H2 to:
=SUMAR.SI.CONJUNTO(Occurrences!F:F;Occurrences!A:A;A2;Occurrences!I:I;"<"&90) -----> in english: =SUMIFS(Occurrences!F:F,Occurrences!A:A,A2,Occurrences!I:I,"<"&90)
Your formula was taking rolled off entries since you didn't include any condition regarding that.

BTW, in worksheet Roster is it missing a column "English", isn't it? Or just an "X" in column F is only an attribute not discriminative?

For column I results, do you think you could be able to build a 2 column table with point ranges in 1st column and description in 2nd one, and place a search formula on it? Si no puede, por favor avise.

Regards!

PS: If you can't, please advise (for the community).
Click to expand...

Sir. Si, si se puede. would you be so kind and show me how. Gracias. Thanks

EDITED (SirJB7)

Sí, si se puede, ... Yes, if it's possible, ...
 
SirJB7 said:
Hi, Yitzhackm!
And what about the <=240 and >241? Please clarify that.
Regards!
Click to expand...
if is TAR or EO but within 240 minutes (4 hours) is 0.5 points if is over than 4 hours (241 minutes) is full point. hope makes sense.
BTW the title in Column F has been replaced with "Bilingual" and assigned a check mark instead :)
 
SirJB7 said:
Hi, Yitzhackm!
And what about the <=240 and >241? Please clarify that.
Regards!
Click to expand...


and that will be based on the number of minutes on column D occurrences tab. I guess that implicates to create a formula to return in column F occurrences tab either 0.5 or 1 based on the amount of minutes entered in Column D
 
Hi, Yitzhackm!

Give a look at this file:
https://dl.dropboxusercontent.com/u...0 25 2013 (for Yitzhackm at chandoo.org).xlsx

Changes:
a) Added a 3rd worksheet with the relation between points and description.
b) 1st worksheet: strange thing, but row 5 doesn't have "x" at column F (if I were the boss, ... deletion!) :)
c) Column H: formula as from above post.
d) Column I: =BUSCARV(H2;Hoja1!A:B;2;VERDADERO) -----> in english: =VLOOKUP(H2,Hoja1!A:B,2,VERDADERO)
e) 2nd worksheet, column F (bonus):
=SI(O(ESPACIOS(C2)="NCNS";ESPACIOS(C2)="ABS";Y(O(ESPACIOS(C2)="TAR";ESPACIOS(C2)="EO");D2>240));1;0,5) -----> in english: =IF(OR(TRIM(C2)="NCNS",TRIM(C2)="ABS",AND(OR(TRIM(C2)="TAR",TRIM(C2)="EO"),D2>240)),1,0.5)
You can remove the TRIM functions if your data in column C doesn't contain trailing spaces.

Just advise if any issue.

Regards!
 
SirJB7 said:
Hi, Yitzhackm!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/I need help creating a dynamic formula to tracks the employees attendance points - MAHC_Occurrence_Tracker_MASTER test 10 25 2013 (for Yitzhackm at chandoo.org).xlsx

Changes:
a) Added a 3rd worksheet with the relation between points and description.
b) 1st worksheet: strange thing, but row 5 doesn't have "x" at column F (if I were the boss, ... deletion!) :)
c) Column H: formula as from above post.
d) Column I: =BUSCARV(H2;Hoja1!A:B;2;VERDADERO) -----> in english: =VLOOKUP(H2,Hoja1!A:B,2,VERDADERO)
e) 2nd worksheet, column F (bonus):
=SI(O(ESPACIOS(C2)="NCNS";ESPACIOS(C2)="ABS";Y(O(ESPACIOS(C2)="TAR";ESPACIOS(C2)="EO");D2>240));1;0,5) -----> in english: =IF(OR(TRIM(C2)="NCNS",TRIM(C2)="ABS",AND(OR(TRIM(C2)="TAR",TRIM(C2)="EO"),D2>240)),1,0.5)
You can remove the TRIM functions if your data in column C doesn't contain trailing spaces.

Just advise if any issue.

Regards!
Click to expand...

Sir JB7, Muchisimas gracias. I really appreciate the help. it works perfect. Le agradesco su ayuda. :)
 
Hi, Yitzhackm!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Hi there,
I know this thread is very old but this actually will solve my problem if I know how to add more segment codes. I have a similar need but with different codes and points such as:
AB= 1point
S= 1point
LE4=1 point
LI4=1 point
LE=1/3 point
LI=1/3 point
MIP= 1/3 point
MOP= 1/3 point

4 to 4 2/3 points would be a written
5-5 2/3 points would be a Final Written
6+ points would be a Termination

I am not at an expert in or close to it in excel but help will solve a problem.
Thanks,
 
You must log in or register to reply here.
Back
Top