Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Commissions data base HELP
I need help programing a data base to figuare out commisions. I need a data
base to multiply 0-250 by $.50 then from 251-500 by $.65 then from 501-750 by $.75 then from 751-1000 by $.85 then from 1001-1500 by $1.00 then from 1501-2000 by $1.15 and then from 2001 + by $1.25. So if someone would enter 2000 I need the data base to multiply the first 250 by $.50 then the next 250 by $.65 then the next 250 by $.75 and so on. Any suggestions will help. Thanks Jason |
#2
|
|||
|
|||
One way:
=SUMPRODUCT(--(A1{0,250,500,750,1000,1500,2000}), (A1-{0,250,500,750,1000,1500,2000}), {0.5,0.15,0.1,0.1,0.15,0.15,0.1}) for an explanation, and more flexible ways of accomplishing this, take a look at http://www.mcgimpsey.com/excel/variablerate.html In article , Jasonroelofs wrote: I need help programing a data base to figuare out commisions. I need a data base to multiply 0-250 by $.50 then from 251-500 by $.65 then from 501-750 by $.75 then from 751-1000 by $.85 then from 1001-1500 by $1.00 then from 1501-2000 by $1.15 and then from 2001 + by $1.25. So if someone would enter 2000 I need the data base to multiply the first 250 by $.50 then the next 250 by $.65 then the next 250 by $.75 and so on. Any suggestions will help. |
#3
|
|||
|
|||
Hi!
See this: http://mcgimpsey.com/excel/variablerate.html Biff "Jasonroelofs" wrote in message ... I need help programing a data base to figuare out commisions. I need a data base to multiply 0-250 by $.50 then from 251-500 by $.65 then from 501-750 by $.75 then from 751-1000 by $.85 then from 1001-1500 by $1.00 then from 1501-2000 by $1.15 and then from 2001 + by $1.25. So if someone would enter 2000 I need the data base to multiply the first 250 by $.50 then the next 250 by $.65 then the next 250 by $.75 and so on. Any suggestions will help. Thanks Jason |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commissions data base HELP
Hello,
I am looking for a similar Commissions Table but a bit more "complicated". I use what we call a Base Commission rate (BCR) for each Account Executive, then we have 2 different Accelerator Commissions rates (ACR1 and ACR2). Also each Account Executive has a Sales Goal (SG) per Quater. If the AE books less or equal to 100% of his sales goal, he gets: Revenue booked*BCR For sales in excess of 100% but inferior to 125%, the ACR1=BCR*1.25 and it is applied to the part of the revenue booked by the AE that is between 100% and 125% of the SG. Then for sales in excess of 125% of the SG, the ACR=BCR*1.50 AND and it is applied to the part of the revenue booked by the AE that is over 125% of the SG. For example if: SG= 800,000.00 Revenue Booked=$1,450,000.00 BCR=2.50% then ACR1=2.5*1.25=3.125% ACR2=2.5%*1.50=3.75% and the guy will earn=(800,000*2.5%)+(200,000*3.13%)+(450,000*3.75% )=$43,135.00 If someone out there could help me figure out how to create my commissions table, I would appreciate it a lot (you have no idea). thanks, Astrid "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(A1{0,250,500,750,1000,1500,2000}), (A1-{0,250,500,750,1000,1500,2000}), {0.5,0.15,0.1,0.1,0.15,0.15,0.1}) for an explanation, and more flexible ways of accomplishing this, take a look at http://www.mcgimpsey.com/excel/variablerate.html In article , Jasonroelofs wrote: I need help programing a data base to figuare out commisions. I need a data base to multiply 0-250 by $.50 then from 251-500 by $.65 then from 501-750 by $.75 then from 751-1000 by $.85 then from 1001-1500 by $1.00 then from 1501-2000 by $1.15 and then from 2001 + by $1.25. So if someone would enter 2000 I need the data base to multiply the first 250 by $.50 then the next 250 by $.65 then the next 250 by $.75 and so on. Any suggestions will help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Commissions data base HELP
You could just modify one of the examples in the reference page that I
gave. Your situation is a very straightforward case of the commissions example listed there. For instance, if your commission table is: J K L 1 Sales Marginal Rate Differential Rate 2 0 2.5% =K2 3 800000 =K2*1.25 =K3-K2 4 =J3*1.25 =K2*1.5 =K4-K3 Then, assuming Revenue Booked is in A1, the formula is simply: =SUMPRODUCT(--(A1$J$2:$J$4),(A1-$J$2:$J$4),$L$2:$L$4) In article , Astrid wrote: If someone out there could help me figure out how to create my commissions table, I would appreciate it a lot (you have no idea). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
How do I compare 2 sets of data and highlight differences? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Adding more source data to existing scatter plot | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |