Need help with an IF formula

[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.

 

[dAVE]

=IF(V39<400,0.33%,IF(V39<600,0.36%,1))

where V39 is the year to date total revenue

 

[Bob Phillips]

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)

 

[Nancy T]

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