Need Help Rolling 6/12 Month Attendance Point Tracking

**khintze** · 03-06-2015, 01:43 PM

Im trying to create a formula that will track employee attendance points for a 6 month and 12 month rolling period. I have attached the spreadsheet. An employee earns back 1/2 (.5) points when they go a month without any occurrences. They can not have less than 0 total points and when I create a formula I can't get it to stop at 0. It keeps adding and going negative which then provides essentially a false positive for an employee. Any help would be greatly appreciated. On the 'SUMMARY' tab is a formula that I tried using but I am missing something and may have overcomplicated the formula so any simplifications would be appreciated too.

**simarui** · 03-06-2015, 02:01 PM

hi khintze, welcome to the forum

i'm having a little trouble understanding your request - where is the formula that is not working for you?

all i see on 'Summary' are vlookups that appear to be working for the most part.

i suspect max() will help you out, something like =max(0,[your other formula here]) will keep your values from going below 0.

**khintze** · 03-06-2015, 02:28 PM

I apologize the incorrect spreadsheet was attached to my original post. The correct sheet is now attached that I am referencing in my initial post. Thank you.

**simarui** · 03-06-2015, 05:46 PM

phew that was fun.

i tried to make that work with formulas for a while but never came up w/ anything... only ideas got reallllly messy in a hurry.

the whole time though i was thinking this should be pretty easy w/ vba (i don't have much experience writing but this was a great opportunity to practice

)

so here you go, two subroutines, one for your 6 month column and one for your "12" month column (you only actually have 10 months of data...)

Please Login or Register  to view this content.

as in the attached. i'm off for the weekend but i'll try to remember to check back in on monday in case you have any questions. someone in the forum can help you out i'm sure.

p.s. i also added an iferror() statement to your vlookups to get rid of those n/a's and an indirect() reference to row 1 so that you can just copy the same formula across all columns. to add next month's data you'll just need to put 'Apr15 in N1 and change the ranges referenced in those codes to columns I through N and D through N.