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.
posted by desjardins to Computers & Internet (8 answers total)
 
Conditionals. If sales > 10 then sales+2 else if sales > 5 sales+1 else sales. (This is pseudocode.)
posted by michaelh at 11:29 AM on February 21, 2012


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]


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


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


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


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


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
=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
=IF(NOT(ISBLANK(A4)),A24+INT(A4/5),"")
posted by desjardins at 12:06 PM on February 21, 2012


« Older Places with cheap rents and don't need a car?   |   Can I smell this? Newer »
This thread is closed to new comments.