I need a formula to do this: if the sale is less than $100 the commission is
15% of the sale, if the sale is less then $200 the commission is 15% on the
first hundred + 10% of the balance of the sale, and if the sale is over $200
the commission is 15% of the first $100 , 10% of the second $100 and 5% for
anything over $200.

I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come near
to what I need.

I need help, I am using Excel 2002.

Harvey Mandel

Maybe something like this:

=IF(A1<100,A1*0.15,IF(A1<200,(100*0.15)+((A1-100)*0.1),(100*0.15)+(100*0.1)+((A1-200)*0.05)))

HTH,
Paul

"Harvey" wrote in message
...
I need a formula to do this: if the sale is less than $100 the commission
is 15% of the sale, if the sale is less then $200 the commission is 15% on
the first hundred + 10% of the balance of the sale, and if the sale is over
$200 the commission is 15% of the first $100 , 10% of the second $100 and
5% for anything over $200.

I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come
near to what I need.

I need help, I am using Excel 2002.

Harvey Mandel

Try:

=IF(A2<=100,A2*0.15,IF(A2<=200,15+(A2-100)*0.1,25+(A2-200)*0.05))

A2=Sale value

Not I have assumed less than or equal to 100, 200: change if needed

"Harvey" wrote:

I need a formula to do this: if the sale is less than $100 the commission is
15% of the sale, if the sale is less then $200 the commission is 15% on the
first hundred + 10% of the balance of the sale, and if the sale is over $200
the commission is 15% of the first $100 , 10% of the second $100 and 5% for
anything over $200.

I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come near
to what I need.

I need help, I am using Excel 2002.

Harvey Mandel

I came up with a bit shorter version:

=IF(D9<=100,D9*0.15,IF(D9<=200,15+(D9-100)*0.1,25+(D9-200)*0.05))

Mark Lincoln

On Jul 10, 2:21 pm, "Harvey" wrote:
I need a formula to do this: if the sale is less than $100 the commission is
15% of the sale, if the sale is less then $200 the commission is 15% on the
first hundred + 10% of the balance of the sale, and if the sale is over $200
the commission is 15% of the first $100 , 10% of the second $100 and 5% for
anything over $200.

I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come near
to what I need.

I need help, I am using Excel 2002.

Harvey Mandel

=IF(A1200,25+(0.05*(A1-200)),IF(A1100,15+(0.1*(A1-100)),IF(A1<=100,(0.15*A1))))

Vaya con Dios,
Chuck, CABGx3



"Harvey" wrote:

I need a formula to do this: if the sale is less than $100 the commission is
15% of the sale, if the sale is less then $200 the commission is 15% on the
first hundred + 10% of the balance of the sale, and if the sale is over $200
the commission is 15% of the first $100 , 10% of the second $100 and 5% for
anything over $200.

I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come near
to what I need.

I need help, I am using Excel 2002.

Harvey Mandel

Hi Harvey,

Look here for a generic solution:

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Harvey" wrote in message ...
|I need a formula to do this: if the sale is less than $100 the commission is
| 15% of the sale, if the sale is less then $200 the commission is 15% on the
| first hundred + 10% of the balance of the sale, and if the sale is over $200
| the commission is 15% of the first $100 , 10% of the second $100 and 5% for
| anything over $200.
|
| I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come near
| to what I need.
|
| I need help, I am using Excel 2002.
|
| Harvey Mandel
|
|

Paul, Thank you and all who responded to my request for help -- the formula
worked perfectly.

Harvey










"PCLIVE" wrote in message
...
Maybe something like this:

=IF(A1<100,A1*0.15,IF(A1<200,(100*0.15)+((A1-100)*0.1),(100*0.15)+(100*0.1)+((A1-200)*0.05)))

HTH,
Paul

"Harvey" wrote in message
...
I need a formula to do this: if the sale is less than $100 the commission
is 15% of the sale, if the sale is less then $200 the commission is 15% on
the first hundred + 10% of the balance of the sale, and if the sale is
over $200 the commission is 15% of the first $100 , 10% of the second $100
and 5% for anything over $200.

I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come
near to what I need.

I need help, I am using Excel 2002.

Harvey Mandel

Thanks for tip on the website it was very helpfull to me.
Harvey



"Niek Otten" wrote in message
...
Hi Harvey,

Look here for a generic solution:

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Harvey" wrote in message
...
|I need a formula to do this: if the sale is less than $100 the commission
is
| 15% of the sale, if the sale is less then $200 the commission is 15% on
the
| first hundred + 10% of the balance of the sale, and if the sale is over
$200
| the commission is 15% of the first $100 , 10% of the second $100 and 5%
for
| anything over $200.
|
| I came up with =IF(D9<100,".15",IF(D9<200,".1","")) which does not come
near
| to what I need.
|
| I need help, I am using Excel 2002.
|
| Harvey Mandel
|
|