Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Calculating mult band sales commission formula

Hi everyone,

I am trying to setup a excel sheet to calculate sales reps commissions, the
more they sell the higher the banding becomes.
<6500 profit = 5% commission
between 6501 & 8000 = 7%
between 8001 & 10'000 = 10
10k to 12.5k = 12%
12.5k to 15k = 15%
over 15k = 20%

Example -
if they earn £10'000 - they get 5% first £6500 (£325), 7% of the next £1500
(£134) etc

Can anyone help me with a formula that will do this for me?

Thanks

Richard
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Calculating mult band sales commission formula

John McGimpsey has a web page on this subject:

http://www.mcgimpsey.com/excel/variablerate.html


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"
m wrote in message
...
Hi everyone,

I am trying to setup a excel sheet to calculate sales reps commissions,

the
more they sell the higher the banding becomes.
<6500 profit = 5% commission
between 6501 & 8000 = 7%
between 8001 & 10'000 = 10
10k to 12.5k = 12%
12.5k to 15k = 15%
over 15k = 20%

Example -
if they earn £10'000 - they get 5% first £6500 (£325), 7% of the next

£1500
(£134) etc

Can anyone help me with a formula that will do this for me?

Thanks

Richard


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default Calculating mult band sales commission formula

Hi,

Create the following table by enter the data in cells A1:B6.

0 5%
6500 7%
8000 10%
10000 12%
12500 15%
15000 20%

Suppose you want to calculate the rate for 9000 in cell D1, enter the formula

=VLOOKUP(D1,$A$1:$B$6,2)

in cell E1 or whereever.

Note that your example is inconsistant. You show <6500 and 6501 - what
happens with 6500? no commission. Since I am not exactly sure which way you
want to go you may need to modify the table a litte. For example you many
need to change 6500 to 6501 and so on.
--
Thanks,
Shane Devenshire


" wrote:

Hi everyone,

I am trying to setup a excel sheet to calculate sales reps commissions, the
more they sell the higher the banding becomes.
<6500 profit = 5% commission
between 6501 & 8000 = 7%
between 8001 & 10'000 = 10
10k to 12.5k = 12%
12.5k to 15k = 15%
over 15k = 20%

Example -
if they earn £10'000 - they get 5% first £6500 (£325), 7% of the next £1500
(£134) etc

Can anyone help me with a formula that will do this for me?

Thanks

Richard

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating sales commission that changes based on a sales volume Elbowes Excel Worksheet Functions 2 June 8th 07 02:48 PM
Formula for determining sales commission Doug Excel Worksheet Functions 2 January 3rd 06 10:50 PM
Sales V commission Carauto Excel Worksheet Functions 2 December 9th 05 02:22 AM
Sales Commission calculations Kevin Excel Worksheet Functions 0 July 25th 05 04:15 PM
sales commission template trey Excel Discussion (Misc queries) 0 December 4th 04 03:32 PM


All times are GMT +1. The time now is 12:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"