Tracking Points that expire
Hi Everybody,
I am trying to track points for HR Department in a simple way. The rules are as follow:
- points expire after 1 year from the time was aquired.
- every 6 months employee receive one wellness day based on perfect attendance - so I have to count point in a specific 6 month period
- every quorter employees receive bonuses calculated based on their points (in a particular quorter/period) - again I need to be able to add the valid points in this period
So far I thought of having one column with names, then entering the date the point was issued and the next cell the number of points and continue alternating date/points. I would make a total using in may case =SUMPRODUCT(MOD(COLUMN(B2:U2),2),B2:U2) that would add only the points and skip the cells that have the date.
Problems: - how do I exclude the points that have expired and sum up? I know I can have a sum if the date is less than 1 yera from today, but how can I do it for every cell that has the date?
Also how can I put the condition for the dates to be in a certain range, taking in consideration that is every other cell?
Or, is there an easier way to keep track and have all employees in one place?
By now they kept a spreadsheet for each employee and it was a hasle to calculate bonuses and keep track.
I would appreciate any imput.
Thank you!
Amber12,
Welcome to the forum!
Attached is an example workbook based on the criteria you described. It has the following layout:
Column A is used to enter names as points are awarded
When a name is entered in column A, column B should have the date of the awarded points
Column C should have the number of points awarded to that person on that day
Column E contains a list of the unique names from column A (so no repeats)
Column F contains a formula to get the active points to date (so old points aren't counted).
In cell F2 and copied down is this formula:
=SUMIFS(ColC,ColA,E2,ColB,">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
ColA, ColB, and ColC are dynamic named ranges so you can just keeping adding rows to the end of the data and the formulas will update automatically. They are defined with the following formula:
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)
Change the A's to B's for the formula to define named range ColB, and the A's to C's for the formula to defined named range ColC.
Is something like that what you're looking for?
Last edited by tigeravatar; 02-03-2012 at 02:36 PM.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Yes, is definitely something that would work. I can’t thank you enough for creating this for me.
For my own knowledge, how or where exactly are you defining ColA and ColB in the way you explained above?
Also, for calculating bonus purpose, what would be the easiest to calculate the valid points in a quarter? So far I moved the totals on a second spreadsheet and apply a filter. I would sort the employees and select the range period that I would need. Still I wouldn’t have the totals. I would have to do that separate. Is it any way that I can have totals the same as you listed for valid points, but defining a certain period?
Thank you again for the time and effort you put in!
To define a name in Excel 2007, go to the Formulas tab on the ribbon and click Name Manager. There you can add/edit/delete named ranges.
As for the quarters, I'm not sure. I'd have to see an example of how they're attained. You mentioned in the original post that they get a bonus based on their existing points. I don't know what that bonus is or how its calculated. If you could upload a sample workbook explaining/showing how it works, I might be able to suggest something for you.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
tigeravatar example for Amber12 (2).xlsx I explained in the attachment and gave an example of what I would have to calculate. Hope I explained well.
Thank you!
Amber12,
See attached.
In Sheet2 I inserted a new row at the top of the data (so headers are now in row 2)
In C1 is the quarter beginning date, 6/13/2011
In D1 is the quarter ending date, 9/11/2011
In C2 is a new column heading, Q Points (to represent the number of points from that quarter)
In D2 is a new column heading, Q Bonus (to represent the bonus based on the Q Points)
In cell C3 and copied down is this formula:
=SUMIFS(ColC,ColA,A3,ColB,">="&$C$1,ColB,"<="&$D$1)
In cell D3 and copied down is this formula:
=CHOOSE(MATCH(C3-0.001,{-1,1,2,3}),0.5,0.4,0.3,0)
Is something like that workable for you?
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
You are a GENIUS! Thank you so much. These days I will actually apply to my real case at work, but I think it will work and is something that I was looking for. I must admit I didn’t work before with these function, even if I’m pretty good with excel.
For future I should be able to just change the dates for the beginning and end of the quarter based on the period that I need, correct?
Oh, I am sooooo happy and so grateful for your help.
Thank you a million times!
That is correct, and you're very welcomeOriginally Posted by Amber12
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
HELP!
A few weeks ago I added some columns and changed some information and I managed to adjust the formulas. Now I see all these errors and I don't undersatnd why. Please help me to see where the error is and what can I do when I add more columns with additional information.
Thank you
Amber12,
Attached is a modified version of your Points final redone workbook. The issue was that the named range ColA wasn't evaluating properly because the named range formula was expecting a header, and Sheet1!A1 was empty. I put in a header (just called it ID) and it is now working properly.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I thought that the header could be an issue, but I ignored it.
Thank you soooo much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks