Need help with an IF formula

  • Thread starter Thread starter Nancy T
  • Start date Start date
N

Nancy T

This is my first post so please bear with me. What I think I need is an
IF formula. I am trying to add YTD revenue to calculate bonuses. When
YTD revenue reaches $400,000, I want to calculate a 33% bonus. At
$600,000 I want to calculate a 35% bonus, at $800,000 it becomes 38%
and at $1,000,000 it becomes 40%.

I only want to calculate the bonus once at each increment. So even if
the YTD revenue exceeds $400,000 for several months in a row, I only
want to add the bonus one time (I'm adding it to the expense line).

I have my dates as column headings with the revenue and expenses as the
rows.

Here's what I've got so far:

=SUM(B41:J41>400000,(SUM(B41:J41)-400000)*0.33)

This works for the first month YTD revenue exceeds $400,000 but not
after that.

Can anyone please help?? Thanks.
 
Nancy,

Firstly create this table in D49,E53

0 0%
400000 33%
600000 35%
800000 38%
1000000 40%

and then put this formula in B43 and copy across

=IF(MATCH(SUM($A41:B41),$D$49:$D$53)<>MATCH(SUM($A41:A41),$D$49:$D$53),(SUM(
$A41:B41)-INDEX($D49:$D53,MATCH(SUM($A41:B41),$D49:$D53),1))*(VLOOKUP(SUM($A
41:B41),$D$49:$E$53,2)),"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the help!

Bob, your solution works. Unfortunately, now that I am looking at it,
need it to do something additional. I'd like the formula to calculat
the percentage each month based on the change from the prior month. S
in my example, Jan rev is 411,832 so the bonus would be 3,905 (whic
you already provided). Feb rev is 501,832 so a bonus of 33% would b
paid on the difference ($90k) and so on.

Is that possible??

Also, can you explain what the $ in the cell reference mean?

Thanks, Nanc
 
Back
Top