Baseline Formula
 

Excel 2007
I am trying to get a formula to do the following commision;
If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that
amount.
If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus
5% of the amount over the $2,000.00 baseline.

Baseline Formula
 

=IF(A1<=2000,A1*2%,40+(A1-2000)*5%)
or
(A1*2%)+(A12000)*(A1-2000)*5%
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"George" wrote in message
...
Excel 2007
I am trying to get a formula to do the following commision;
If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that
amount.
If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00
plus
5% of the amount over the $2,000.00 baseline.

Baseline Formula
 

Something is wrong, when I am equal to or below $2,000.00 it's fine, but when
I go above $2,000.00 it shows a negative number. It should show the 2% plus
the 5% above the $2,000.00

"Bernard Liengme" wrote:

=IF(A1<=2000,A1*2%,40+(A1-2000)*5%)
or
(A1*2%)+(A12000)*(A1-2000)*5%
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"George" wrote in message
...
Excel 2007
I am trying to get a formula to do the following commision;
If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that
amount.
If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00
plus
5% of the amount over the $2,000.00 baseline.

Baseline Formula
 

Wouldn't your second formula need to be either
=MIN(A3,2000)*2%+(A32000)*(A3-2000)*5% or
=(A1*2%)+(A12000)*(A1-2000)*3% , Bernard?
--
David Biddulph

Bernard Liengme wrote:
=IF(A1<=2000,A1*2%,40+(A1-2000)*5%)
or
(A1*2%)+(A12000)*(A1-2000)*5%
best wishes

"George" wrote in message
...
Excel 2007
I am trying to get a formula to do the following commision;
If A1 is less then or equal to $2,000.00 then B2 would equal 2% of
that amount.
If A1 is greater then $2,000.00 then B2 would equal 2% of the
$2,000.00 plus
5% of the amount over the $2,000.00 baseline.

Baseline Formula
 

I was too hasty; yes we need
=A1*2%+(A12000)*(A1-2000)*3%
thanks for the heads up!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Wouldn't your second formula need to be either
=MIN(A3,2000)*2%+(A32000)*(A3-2000)*5% or
=(A1*2%)+(A12000)*(A1-2000)*3% , Bernard?
--
David Biddulph

Bernard Liengme wrote:
=IF(A1<=2000,A1*2%,40+(A1-2000)*5%)
or
(A1*2%)+(A12000)*(A1-2000)*5%
best wishes

"George" wrote in message
...
Excel 2007
I am trying to get a formula to do the following commision;
If A1 is less then or equal to $2,000.00 then B2 would equal 2% of
that amount.
If A1 is greater then $2,000.00 then B2 would equal 2% of the
$2,000.00 plus
5% of the amount over the $2,000.00 baseline.

Baseline Formula
 

Here's another formula that would work

=MIN(0.02*A1,40)+MAX(0.05*(A1-2000),0)

"George" wrote:

Excel 2007
I am trying to get a formula to do the following commision;
If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that
amount.
If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus
5% of the amount over the $2,000.00 baseline.

Baseline Formula
 

=MIN(0.02*A1,40)+MAX(0.05*(A1-2000),0)

Another option...

= Max(0.02*A1, 0.05*A1 - 60)

= = =
Dana DeLouis



MrAcquire wrote:
Here's another formula that would work

=MIN(0.02*A1,40)+MAX(0.05*(A1-2000),0)

"George" wrote:

Excel 2007
I am trying to get a formula to do the following commision;
If A1 is less then or equal to $2,000.00 then B2 would equal 2% of that
amount.
If A1 is greater then $2,000.00 then B2 would equal 2% of the $2,000.00 plus
5% of the amount over the $2,000.00 baseline.