Help with Excel formula and table
February 21, 2012 11:27 AM Subscribe
I need to a formula that will calculate accumulated points based on the number of sales an employee had during a quarter, when there are certain milestones he or she has met.
For each sale, the employee gets one point. After 5 sales in a quarter, they get an additional bonus point. After ten sales, another additional point.
So, if Joe sells 5 widgets in January, 6 in February and 10 in March, he would have a total of 21 regular points and 4 bonus points for a total of 25.
I have a flat table like so:
Name -- Date -- Sale -- Points
----------------------------------------
Joe -- 1/1/2012 - Acme -- 1
Joe - 1/2/2012 - Example -- 1
Joe - 1/3/2012 - Test -- 1
Joe - 1/4/2012 - Google -- 1
Joe -- 1/5/2012 -- Apple -- 1
Herbert -- 1/1/2012 -- Microsoft -- 1
Herbert -- 1/2/2012 -- Facebook -- 1
etc.
I want a table that's the equivalent of "Joe earned 5 points in January plus 1 bonus point. Herbert earned 2 points in January." I don't care who the sale was to or what they sold. I know how to use a pivot table to get the regular points. I can't figure out how to add in the bonus points.
Please for the love of God don't tell me to use Access. I know it's probably a better tool for this but it's just not going to happen at this point.
For each sale, the employee gets one point. After 5 sales in a quarter, they get an additional bonus point. After ten sales, another additional point.
So, if Joe sells 5 widgets in January, 6 in February and 10 in March, he would have a total of 21 regular points and 4 bonus points for a total of 25.
I have a flat table like so:
Name -- Date -- Sale -- Points
----------------------------------------
Joe -- 1/1/2012 - Acme -- 1
Joe - 1/2/2012 - Example -- 1
Joe - 1/3/2012 - Test -- 1
Joe - 1/4/2012 - Google -- 1
Joe -- 1/5/2012 -- Apple -- 1
Herbert -- 1/1/2012 -- Microsoft -- 1
Herbert -- 1/2/2012 -- Facebook -- 1
etc.
I want a table that's the equivalent of "Joe earned 5 points in January plus 1 bonus point. Herbert earned 2 points in January." I don't care who the sale was to or what they sold. I know how to use a pivot table to get the regular points. I can't figure out how to add in the bonus points.
Please for the love of God don't tell me to use Access. I know it's probably a better tool for this but it's just not going to happen at this point.
Best answer: Assuming you're using excel, it might be points=sales+int(sales/5)
posted by jon1270 at 11:32 AM on February 21, 2012 [1 favorite]
posted by jon1270 at 11:32 AM on February 21, 2012 [1 favorite]
Use IF statements!
For example, separate the quarters and do a sum for each quarter. Then you can write a short if statement, like this:
=if(sum(range of quartile data points)>5,int(sum(range)/5),0)
Which are formatted in the following way:
if("conditional equality", "if the answer is yes, do this", "if the answer is no, do this")
In my example, this translates to:
"Is the sum of their sales larger than ten? If the answer is yes, find the integer of their total points for the quarter divided by 5 (adding the "int" thing prevents getting decimal answers, only whole number points). If the answer is no, the total is zero."
posted by Paper rabies at 11:38 AM on February 21, 2012
For example, separate the quarters and do a sum for each quarter. Then you can write a short if statement, like this:
=if(sum(range of quartile data points)>5,int(sum(range)/5),0)
Which are formatted in the following way:
if("conditional equality", "if the answer is yes, do this", "if the answer is no, do this")
In my example, this translates to:
"Is the sum of their sales larger than ten? If the answer is yes, find the integer of their total points for the quarter divided by 5 (adding the "int" thing prevents getting decimal answers, only whole number points). If the answer is no, the total is zero."
posted by Paper rabies at 11:38 AM on February 21, 2012
Response by poster: Do I add another column to the flat table? I'm not sure how that works with a pivot table.
Excel 2010
posted by desjardins at 11:39 AM on February 21, 2012
Excel 2010
posted by desjardins at 11:39 AM on February 21, 2012
Oh shoot, jon's is actually way easier. Duh. I always overcomplicate Excel.
posted by Paper rabies at 11:39 AM on February 21, 2012
posted by Paper rabies at 11:39 AM on February 21, 2012
How do you need to display this info? You can add a column after the pivot table, for instance, with one of the ideas given above. Or you can use a formula in the pivot table to have an extra row per salesperson, I think, if you show sales by quarter. It might be easiest to have a few rows at the top (one per salesperson) that does a sumif on points per salesperson. But mostly it depends on how you need to use the info.
posted by jeather at 11:56 AM on February 21, 2012
posted by jeather at 11:56 AM on February 21, 2012
Response by poster: So I was able to do this by referencing cells in the existing pivot table: (row values are quarter and employee name, values are count of points)
How can I not have zeros show up in the subheadings (Quarter 1, Quarter 2)?
For example, the new table looks like
Quarter one
Joe 13
Herbert 9
Quarter two 0
Joe 9
Herbert 6
Quarter 3 0
etc
I need to not have the zeros next to Quarter 2, etc
I have to drag the formulas down the rows or I'll have to do this every time the range expands, right?
here's my formula that doesn't work to eliminate the zeros
posted by desjardins at 12:03 PM on February 21, 2012
How can I not have zeros show up in the subheadings (Quarter 1, Quarter 2)?
For example, the new table looks like
Quarter one
Joe 13
Herbert 9
Quarter two 0
Joe 9
Herbert 6
Quarter 3 0
etc
I need to not have the zeros next to Quarter 2, etc
I have to drag the formulas down the rows or I'll have to do this every time the range expands, right?
here's my formula that doesn't work to eliminate the zeros
=IF(NOT(0),A4+INT(A4/5),"")
posted by desjardins at 12:03 PM on February 21, 2012
Response by poster: never mind on that last question - the correct code was
posted by desjardins at 12:06 PM on February 21, 2012
=IF(NOT(ISBLANK(A4)),A24+INT(A4/5),"")
posted by desjardins at 12:06 PM on February 21, 2012
This thread is closed to new comments.
posted by michaelh at 11:29 AM on February 21, 2012