How to Calculate Bonus in Excel (5 Handy Methods)

Here we’ve got a dataset of the Daily Sales List of Sales Reps and their Daily Sales. Also, have a Sales Hurdle of $15,000 and a Bonus Percentage of 10%, which means that exceeding the minimum limit of $15,000 adds a 10% bonus to the rest.

Method 1 – Using IF Function to Calculate Bonus in Excel

Steps:

• Select cell D8.
• Copy the formula below, and press Enter:

Note: Here “–” sign means zero or not applicable.

• Use the Fill Handle tool to get the remaining results of column D.

You can reverse the order in the IF and get similar results.

Steps:

• Input the following into D8 and press Enter:

We can see that the results come here are as same as those applied in the case of true values.

Method 2 – Applying MAX Function to Calculate Bonus in Excel

Steps:

• Select cell D8.
• Paste down the following formula and press Enter:

• Fill in the other cells via the fill handle.

Formula Breakdown

The formula calculates the bonus based on the current payment. If the sales are lower than the threshold, the bonus becomes negative. The MAX function replaces that with 0 (since it’s larger than a negative number).

Method 3 – Engaging Boolean Logic

Steps:

• Use the following formula in cell D8 and press Enter:

Formula Breakdown

(C8>=$C$4) is a boolean statement that returns TRUE or FALSE. Computers save this as numbers 1 and 0, respectively.

So, the logic we put here is that the daily sales amount has to be greater than or equal to the sales hurdle. If any sales rep fulfills the condition, then the result will be true, and as a result, his bonus will be multiplied by 1. On the contrary, if someone fails to meet the criteria, his negative bonus amount will be multiplied by 0 accordingly.

Method 4 – Implementing VLOOKUP Function

For this method, we made slight changes in our dataset. Previously, we fixed our sales hurdle at $15,000 with a 10% bonus. We have added another limit of $10,000 with a bonus percentage of 5% and put in the 0% bonus as the baseline. We also decreased the daily sales amount of Nick and Leon.

Steps:

• Select cell D10, type down the formula below, and press Enter:

• Use the Fill Handle tool and by dragging it down got the other values of column D.

Method 5 – Employing VBA Code to Calculate Bonus in Excel

Steps:

• Right-click on the Sheet name and select View Code.

• From Toggle Folders, right-click on Sheet7 (VBA), select Insert, then Module.

• Paste the code below into the editor.

Sub Bonus()
Dim LastRow As Long
With ThisWorkbook.Worksheets("VBA")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("D8:D" & LastRow) = "=IF(C8>=$C$4,(C8-$C$4)*.1,0)"
End With
End Sub

• Click on the Run button or press F5.
• Close the code module and return to the worksheet.

The cells of column D are automatically filled up with the correct result as our method 1. Select cell D8 and you can see the exact formula which we used in our method 1 in the Formula Bar.

In our VBA code, we used the exact same formula as method 1. For using the IF function here, the results came is also the same.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

<< Go Back to Commission Bonus | Formula List | Learn Excel