Logical Function - Multiple IF statements

Can anyone help me figure out a formula for the following sliding bonus
scenario:

To receive a bonus, Gross Profit must be at least $250,000
Once minimum is met, bonuses are calculated as a percent of the Adjusted GP
(GP minus $250,000) as follows:

If Actual GP is under $500,000:
3% of Adjusted GP

If Actual GP is $500,000 up to $750,000:
3% of first $250,000
4% of balance of Adjusted GP

If Actual GP is over $750,000:
3% of first $250,000
4% of second $250,000
4.5% of balance of Adjusted GP

Thanks!!

See here for an example:

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

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Can anyone help me figure out a formula for the
following sliding bonus
>scenario:
>
>To receive a bonus, Gross Profit must be at least
$250,000
>Once minimum is met, bonuses are calculated as a percent
of the Adjusted GP
>(GP minus $250,000) as follows:
>
>If Actual GP is under $500,000:
>3% of Adjusted GP
>
>If Actual GP is $500,000 up to $750,000:
>3% of first $250,000
>4% of balance of Adjusted GP
>
>If Actual GP is over $750,000:
>3% of first $250,000
>4% of second $250,000
>4.5% of balance of Adjusted GP
>
>Thanks!!
>.
>

Thanks, but I don't think the SUMPRODUCT function will work because I'm not
going to have the data listed...

In my spreadsheet, a person will type in the Actual Gross Profit in cell D29
and then the result of the calculation (where the formula is) will be in cell
D31.

"Jason Morin" wrote:

> See here for an example:
>
> http://www.mcgimpsey.com/excel/variablerate.html
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Can anyone help me figure out a formula for the
> following sliding bonus
> >scenario:
> >
> >To receive a bonus, Gross Profit must be at least
> $250,000
> >Once minimum is met, bonuses are calculated as a percent
> of the Adjusted GP
> >(GP minus $250,000) as follows:
> >
> >If Actual GP is under $500,000:
> >3% of Adjusted GP
> >
> >If Actual GP is $500,000 up to $750,000:
> >3% of first $250,000
> >4% of balance of Adjusted GP
> >
> >If Actual GP is over $750,000:
> >3% of first $250,000
> >4% of second $250,000
> >4.5% of balance of Adjusted GP
> >
> >Thanks!!
> >.
> >
>

Did you try it?

The whole article is based on entering your AGP in one cell...

Your example is a rather straightforward application of the first
technique shown:

=IF(D29>=250000,SUMPRODUCT(--(D29>{0,500000,750000}),
(D29-{0,500000,750000}),{0.03,0.01,0.005}),0)

The remaining techniques make things more flexible, but aren't required.


In article <[email protected]>,
"vnsrod2000" <[email protected]> wrote:

> Thanks, but I don't think the SUMPRODUCT function will work because I'm not
> going to have the data listed...
>
> In my spreadsheet, a person will type in the Actual Gross Profit in cell D29
> and then the result of the calculation (where the formula is) will be in cell
> D31.

Sorry, I see now that it is applicable...However, maybe because I'm not as
savvy with the formulas, I cannot get it to work. The one you include doesn't
calculate properly(if it was intended to). If actual GP is $850,000, then the
result should be $22,000.

Any further assistance is appreicated....Thanks.

"JE McGimpsey" wrote:

> Did you try it?
>
> The whole article is based on entering your AGP in one cell...
>
> Your example is a rather straightforward application of the first
> technique shown:
>
> =IF(D29>=250000,SUMPRODUCT(--(D29>{0,500000,750000}),
> (D29-{0,500000,750000}),{0.03,0.01,0.005}),0)
>
> The remaining techniques make things more flexible, but aren't required.
>
>
> In article <[email protected]>,
> "vnsrod2000" <[email protected]> wrote:
>
> > Thanks, but I don't think the SUMPRODUCT function will work because I'm not
> > going to have the data listed...
> >
> > In my spreadsheet, a person will type in the Actual Gross Profit in cell D29
> > and then the result of the calculation (where the formula is) will be in cell
> > D31.
>

vnsrod2000 wrote...
>Sorry, I see now that it is applicable...However, maybe because I'm
not as
>savvy with the formulas, I cannot get it to work. The one you include
doesn't
>calculate properly(if it was intended to). If actual GP is $850,000,
then the
>result should be $22,000.
>
>Any further assistance is appreicated....Thanks.
....

JE may have been a little hasty in his specific formula, but the
technique is sound. Try

=SUMPRODUCT(--(GP>{250,500,750}*1000),GP-{250,500,750}*1000,{0.03,0.01,0.005})

Seems to be working brilliantly!
Thanks Harlan
Thanks JE
Thanks Jason


"Harlan Grove" wrote:

> vnsrod2000 wrote...
> >Sorry, I see now that it is applicable...However, maybe because I'm
> not as
> >savvy with the formulas, I cannot get it to work. The one you include
> doesn't
> >calculate properly(if it was intended to). If actual GP is $850,000,
> then the
> >result should be $22,000.
> >
> >Any further assistance is appreicated....Thanks.
> ....
>
> JE may have been a little hasty in his specific formula, but the
> technique is sound. Try
>
> =SUMPRODUCT(--(GP>{250,500,750}*1000),GP-{250,500,750}*1000,{0.03,0.01,0.005})
>
>

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> JE may have been a little hasty in his specific formula

Indeed. Thanks for the correction!