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

**MinieMe209** · 11-12-2013, 08:14 AM

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.

**MinieMe209** · 11-12-2013, 08:27 AM

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

**benishiryo** · 11-12-2013, 08:40 AM

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

**MinieMe209** · 11-12-2013, 08:58 AM

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?

I've already implemented your changes on the spreadsheet this is how it looks:

Sample FSS Attendance Point System Close to Finished!.xlsx

**MinieMe209** · 11-12-2013, 09:33 AM

Anyone got any ideas?

**benishiryo** · 11-12-2013, 10:43 AM

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

**MinieMe209** · 11-13-2013, 11:41 AM

Thanks!

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

. .

**BigDawg15** · 11-13-2013, 03:20 PM

MinieMe209,

Try this in Nov L6 and copied down:

Please Login or Register  to view this content.

BigDawg15

**MinieMe209** · 11-13-2013, 05:15 PM

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

**BigDawg15** · 11-13-2013, 05:36 PM

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

**mostafalsayed** · 03-17-2016, 05:34 AM

thanks very m

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Attendance Point System Spreadsheet with Rolling 1-year Points Reversal

[SOLVED] How can I create a point system for attendance, tardies, absents, etc.?

Point System Attendance - Pivot Table Assistance

Keeping track of attendance point system

30 Days Perfect Attendance

Bookmarks

Bookmarks

Posting Permissions