 • 443K Members
• 12.9K Online
• 532K Conversations

# Need Help Writing a Formula

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.

7 Replies

# Re: Need Help Writing a Formula

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

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

# Re: Need Help Writing a Formula

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.

# Re: Need Help Writing a Formula

@Twifoo , my negligence, thank you for the correction

# Re: Need Help Writing a Formula

You’re welcome, @Sergei Baklan.

# Re: Need Help Writing a Formula

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

# Re: Need Help Writing a Formula

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`

# Re: Need Help Writing a Formula

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

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies