Variable function

[ESP Tom]

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)

[Sheila D]

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(cellref>4999,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
>
>

[RagDyeR]

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" <[email protected]> wrote in
message news:[email protected]...

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