+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    10-24-2013
    Location
    Stockton
    MS-Off Ver
    2007
    Posts
    20

    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. #2
    Registered User
    Join Date
    10-24-2013
    Location
    Stockton
    MS-Off Ver
    2007
    Posts
    20

    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. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    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

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    10-24-2013
    Location
    Stockton
    MS-Off Ver
    2007
    Posts
    20

    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?

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

    Sample FSS Attendance Point System Close to Finished!.xlsx

  5. #5
    Registered User
    Join Date
    10-24-2013
    Location
    Stockton
    MS-Off Ver
    2007
    Posts
    20

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

    Anyone got any ideas?

  6. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    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. #7
    Registered User
    Join Date
    10-24-2013
    Location
    Stockton
    MS-Off Ver
    2007
    Posts
    20

    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. #8
    Registered User
    Join Date
    02-01-2011
    Location
    California, Sacramento
    MS-Off Ver
    Excel 2010
    Posts
    91

    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. #9
    Registered User
    Join Date
    10-24-2013
    Location
    Stockton
    MS-Off Ver
    2007
    Posts
    20

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

    Quote Originally Posted by BigDawg15 View Post
    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. .
    http://i125.photobucket.com/albums/p...ps75290e22.jpg

  10. #10
    Registered User
    Join Date
    02-01-2011
    Location
    California, Sacramento
    MS-Off Ver
    Excel 2010
    Posts
    91

    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. #11
    Registered User
    Join Date
    03-17-2016
    Location
    qatar
    MS-Off Ver
    2010
    Posts
    1

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

    thanks very m

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Attendance Point System Spreadsheet with Rolling 1-year Points Reversal
    By mfoley9723 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-04-2018, 04:46 PM
  2. Replies: 10
    Last Post: 07-20-2018, 09:55 PM
  3. Point System Attendance - Pivot Table Assistance
    By nikakims in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2014, 07:43 PM
  4. Keeping track of attendance point system
    By okracerx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2013, 11:08 PM
  5. 30 Days Perfect Attendance
    By ForumShy in forum Excel General
    Replies: 6
    Last Post: 02-07-2012, 06:28 PM

Bookmarks

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