Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable function
Hi, I'm a sales manager that has a team with a variable rate of commission. They either make telesales or sales through an appointment. If they make more than £5000 in telesales their commision goes up from 5% - 10% of their total sales. i.e. 0.05 x sum(telesale + appointment sales). At the moment I use a referenced worksheet on my machiene that draws on spreadsheets on the company file. I can programme the sheet to work out the percentage if it's set at 5% but is it possible to make it vary if the telesales value goes above £10'000? i.e. when telesales = £2000-£4999 then 0.05 x sum(telesale + appointment sales) but when telesales = £5000 then 0.10 x sum(telesale + appointment sales) -- ESP Tom ------------------------------------------------------------------------ ESP Tom's Profile: http://www.excelforum.com/member.php...o&userid=36198 View this thread: http://www.excelforum.com/showthread...hreadid=559875 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable function
Tom
You can use the IF function to do this - best to put the commission rates in reference cells so that you can change it easily. Something like =IF(cellref4999,cellref * commission rate,cellref * alternative commission rate) Replace cellref and commission rate with relevant Cell References HTH Sheila www.c-i-m-s.com MS Office training - London "ESP Tom" wrote: Hi, I'm a sales manager that has a team with a variable rate of commission. They either make telesales or sales through an appointment. If they make more than £5000 in telesales their commision goes up from 5% - 10% of their total sales. i.e. 0.05 x sum(telesale + appointment sales). At the moment I use a referenced worksheet on my machiene that draws on spreadsheets on the company file. I can programme the sheet to work out the percentage if it's set at 5% but is it possible to make it vary if the telesales value goes above £10'000? i.e. when telesales = £2000-£4999 then 0.05 x sum(telesale + appointment sales) but when telesales = £5000 then 0.10 x sum(telesale + appointment sales) -- ESP Tom ------------------------------------------------------------------------ ESP Tom's Profile: http://www.excelforum.com/member.php...o&userid=36198 View this thread: http://www.excelforum.com/showthread...hreadid=559875 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable function
Check out this page of J.E. McGimpsey:
http://www.mcgimpsey.com/excel/variablerate.html -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ESP Tom" wrote in message ... Hi, I'm a sales manager that has a team with a variable rate of commission. They either make telesales or sales through an appointment. If they make more than £5000 in telesales their commision goes up from 5% - 10% of their total sales. i.e. 0.05 x sum(telesale + appointment sales). At the moment I use a referenced worksheet on my machiene that draws on spreadsheets on the company file. I can programme the sheet to work out the percentage if it's set at 5% but is it possible to make it vary if the telesales value goes above £10'000? i.e. when telesales = £2000-£4999 then 0.05 x sum(telesale + appointment sales) but when telesales = £5000 then 0.10 x sum(telesale + appointment sales) -- ESP Tom ------------------------------------------------------------------------ ESP Tom's Profile: http://www.excelforum.com/member.php...o&userid=36198 View this thread: http://www.excelforum.com/showthread...hreadid=559875 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable function | Excel Discussion (Misc queries) | |||
Variable function | Excel Discussion (Misc queries) | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Variable values in Index function | Excel Worksheet Functions |