IF and then statements to calculate commissions
I need formulas that will calculate the following:
If we make:
Under 20 sales = $0.00 per sale comm
20-29 sales = $25 per sale comm
30-34 sales = $35 per sale comm
35-39 sales = $45 per sale comm
40 sales = $75 per sale comm
40+ sales = 85 per sale comm.
I was using the < and > signs but I didn't get anywhere.
Thank you,
trosasco23
How about:
Cheers,
Please Login or Register to view this content.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
This will work. Change A1 to what the cell is that has the value and enter this formula where you want the answer
Formula:
Please Login or Register to view this content.
This also works
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 09-22-2014 at 01:28 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
is that on total sales ?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
the lookups work but i need to get a total per person later. this is going into a pivot
to martindwilson-it is per sale yes.
You could add at Calculated Field in your pivot table to add this function in from the raw data being summarized.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Are there any other things that you haven't said about this problem? You can have the commission on the source data for your Pivot Table or you can add it to the Pivot table with a Calculated Field as JBeaucaire stated.
If there are further complications please upload a workbook representative of the problem.
To attach a workbook:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
This is the report I'm working on. I added the lookup formula on the yellow tab called LIST.. I can't figure out how to incorporate this if a rep makes a sale to calculate their commissions. they get paid per month.
On the Commissions by Rep tab, you can group the dates by month, days, etc. to get the stats that you want for a period of time. To get data by month, click on Commissions by Rep tab, C5, right click and select GROUP then select MONTHS leaving your other selections alone. This will insert a column for months. Collapse the months so that the days are not showing and you have monthly data.
321 Loans Commissions.xlsx
If I group anything on the INPUT tab to get totals that way, my pivots will include those totals and they will multiply (hence giving me incorrect data). I deleted the commissions tab from the INPUT screen. I would need a formula that would give me the info as a calculated field as JBeaucaire mentioned. But what would my formula be? It would go in my pivot table on the BY REP tab. For example (Mr. Alan Levine made two sales but since its under 20, he doesnt get a commission). I named the LIST of the commissions on the LIST tab. Please help.. anyone anyone?
Thank you so much.
Last edited by trosasco23; 09-22-2014 at 04:51 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks