Need Help Writing a Formula

[dav7922]

I need help writing a formula based on this written out process to figure my staff's monthly bonuses.  I am not sure whether I should use  IF, IFS, or VLookUp formulas.

Step 1: Is Current Month >= Prior Year's Month by 8%? No, then move to Step 2.  Yes, then multiply Current Month by 4%.

Step 2: Is Current Month >= Prior Year's Month by 6%? No, then move to Step 3.  Yes, then multiply Current Month by 3%.

Step 3: Is Current Month >= Prior Year's Month by 4%?  No, then move to Step 4.  Yes, then multiply Current Month by 2%.

Step 4:  Is Current Month >= Prior Year's Month by 2%?  No, then No Bonus.  Yes, then multiply Current Month by 1%.

Thank you for the help.

[#2]

@dav7922 

If in A1 is current to prev month %, when bonus % could be

=MAX(MIN(INT(A1/0.02)/100,0.08),0)

[#3]

@dav7922 

If Current, Prior, and Change are in A2, B2, and C2, respectively, this formula in D2 returns your desired result: 

=(C2>=0.02)*

MIN(FLOOR(C2/2,0.01),

0.04)

Using the formula of @Sergei Baklan , the formula in D2 would be like this: 

=MAX(MIN(INT(C2/0.02)/100,

0.04),

0)

Note that the number2 argument of MIN should have been 0.04 instead of 0.08, which I guess was a typographical error. 

[#4]

@Twifoo , my negligence, thank you for the correction

[#5]

You’re welcome, @Sergei Baklan.

[#6]

@dav7922 

Gentlemen,

Thank you for the two formulas.  I tried them both and they both returned the same number but it is not what I am looking for.  Here is what I did based on what I understood from your responses.

Current Year A2        Previous Year B2      Difference C2      Formula D2

1000                          800                           200                      0.04

1000                           800                          200                       0.04

[#7]

@dav7922 

I suggest using a VLOOKUP formula with bracket lookup. You need to build a lookup table with brackets for different values of the ratio between current month and prior year same month sales. I put mine in cells Q2:R6

If A1 is current month sales and A2 is prior year same month sales, the bonus may be found as:

=VLOOKUP(A1/A2,$Q$2:$R$6,2)*A1

[#8]

@dav7922 

You shall multiply received % in D2 on current sales in A2 to receive the bonus to pay if that's how it is calculated.

Both formulas return per cent as it was asked (4%, or 0.04 in your sample).