I am having trouble wrapping my brain around this one:

I want to create some sales scenarios using scales , eg

You get 3% of sales for the first 1000 widgets
you get 2% for the next 3000 widgets
you get 1% for all widgets sold in excess of 4000

Of course I want the numbers to be 'knobs' so I can tune my scenario into
something that works.

I see it being pretty ugly using formulas

Is there an easy or elegant way to do this?



Thanks

Bill

I did not really explain what I want to do with this so here goes;

using my algorithm below, I want to list commision dollars ofr various sales

so assuming each widget costs $1 then

Sales qty Commission
1000 1 x 1000 x 3% = 30
2000 ( 1 x 2000 x 3% = 60 + 1x 1000 x 2% = 20) = 80


Bill





"Bill" wrote in message
...
I am having trouble wrapping my brain around this one:

I want to create some sales scenarios using scales , eg

You get 3% of sales for the first 1000 widgets
you get 2% for the next 3000 widgets
you get 1% for all widgets sold in excess of 4000

Of course I want the numbers to be 'knobs' so I can tune my scenario into
something that works.

I see it being pretty ugly using formulas

Is there an easy or elegant way to do this?



Thanks

Bill

Hi Bill

One way
With Number sold in B1
=B1*3%+MAX(0,(B1-1000)*2%)+MAX(0,(B1-4000)*1%)

If you wanted to be able to easily vary the break points for commission,
you could put 1000 in A and 4000 in A2 then change formula to
=B1*3%+MAX(0,(B1-$A$1)*2%)+MAX(0,(B1-$A$2)*1%)

--
Regards

Roger Govier


"Bill" wrote in message
...
I did not really explain what I want to do with this so here goes;

using my algorithm below, I want to list commision dollars ofr various
sales

so assuming each widget costs $1 then

Sales qty Commission
1000 1 x 1000 x 3% = 30
2000 ( 1 x 2000 x 3% = 60 + 1x 1000 x 2% = 20) = 80


Bill





"Bill" wrote in message
...
I am having trouble wrapping my brain around this one:

I want to create some sales scenarios using scales , eg

You get 3% of sales for the first 1000 widgets
you get 2% for the next 3000 widgets
you get 1% for all widgets sold in excess of 4000

Of course I want the numbers to be 'knobs' so I can tune my scenario
into something that works.

I see it being pretty ugly using formulas

Is there an easy or elegant way to do this?



Thanks

Bill

Take a look he

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


In article ,
"Bill" wrote:

I am having trouble wrapping my brain around this one:

I want to create some sales scenarios using scales , eg

You get 3% of sales for the first 1000 widgets
you get 2% for the next 3000 widgets
you get 1% for all widgets sold in excess of 4000

Of course I want the numbers to be 'knobs' so I can tune my scenario into
something that works.

I see it being pretty ugly using formulas

Is there an easy or elegant way to do this?



Thanks

Bill

Thanks

That is just what I needed!

Bill



"JE McGimpsey" wrote in message
...
Take a look he

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