# Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attendance

1. ## Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attendance

So here's what I'm working on.

I'm working on an attendance system that'll add points depending on the incident.

So far I've been able to come up with the calculations to add the points for the month. The last bit I'm having trouble with is subtracting points for perfect attendance.

What I'm trying to do is get excel to subtract 2 points from the running total if there are no occurrences for that month.

Or if there's a way to be able to date the last occurrence and automatically deduct 2 points if there are no attendance issues 30 days thereafter.

FSS Attendance Tracking.xlsx

BTW I am not claiming to have came up with the formula on my own. I found a similar post to this on here and modified the information to fit my needs.

Thanks to the original poster and the people who help him/her.

2. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

Sorry that was the original concept I had, this is the one I've been working on:
Sample FSS Attendance Point System.xlsx

3. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

welcome to the forum, MinieMe. you can shorten your formula in Oct K4 to:
=SUMPRODUCT((\$D\$3:\$I\$3=\$N\$3:\$N\$8)*D4:I4*\$O\$3:\$O\$8)

not sure how is the deduction done. you have done a great job of uploading a file & show us how the positive points are assigned. you could illustrate better by showing the desired results of the scenario you wish to see. to deduct 2 points from the row of the person when there are nothing filled in row 4 would be:
=IF(COUNT(D4:I4)=0,-2,SUMPRODUCT((\$D\$3:\$I\$3=\$N\$3:\$N\$8)*D4:I4*\$O\$3:\$O\$8))

with the above formula, you could probably adjust the range a little if you need to look at everyone

4. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

Thanks for the welcoming! This is a whole nother world I had no idea how sophisticated excel could be! I will be frequenting this site very often!

Now, that's a great job you did on the formula. My only other question is, is there a way to make it so that the total and running total don't go negative?

For example, If you have perfect attendance for two months your points will remain at 0 as opposed as going to -4?

Sample FSS Attendance Point System Close to Finished!.xlsx

5. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

Anyone got any ideas?

6. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

this is quite manual, but i couldn't think of a shorter way. so in Nov L6:
=October!L4+K6-MAX(0,SUM(--(October!K4=-2),--(K6=-2))-1)*-2

7. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

Thanks!

I was still getting some -2's from the month of October though . .

8. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

MinieMe209,

Try this in Nov L6 and copied down:

``Please Login or Register  to view this content.``
BigDawg15

9. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

Originally Posted by BigDawg15
MinieMe209,

Try this in Nov L6 and copied down:

``Please Login or Register  to view this content.``
BigDawg15
Thanks that worked GREAT!

Now, the the sense of making it look pretty, is there a way to make the Value of the K column never go below 0?

The highlighted column. .
\1

10. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

MinieMe209,

The only thing I can suggest is you can hide Column K. Because you are using column K in part of the formula (for column L), anything that you do to it may alter the result.

Someone with more experience than I may have a better solution, but that would be out of my league.

Regards,

BigDawg15

11. ## Re: Attendance System that Adds Point For the Month and Subtracts Points if Perfect Attend

thanks very m

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1