This article shows how to calculate the bonus in Excel. More precisely, here we will calculate the sales bonus in Excel. Normally, employees are rewarded for meeting job goals, doing particularly well, and completing milestones through sales bonus schemes. Here, we will take you through 5 easy and convenient methods on how to calculate the bonus in Excel.

**Table of Contents**hide

## Download Practice Workbook

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

## 5 Methods to Calculate Bonus in Excel

Previously mentioned that we intended to calculate sales bonuses in Excel. But, *do you have knowledge of what a sales bonus is and why it is essential*? Let’s go through this matter first.

A sales bonus is a monetary reward given to a sales representative who meets or surpasses a pre-determined target. For example, if he had a target of $10,000 daily sales and he made it $12,000, he will get a percentage of the sales amount as a bonus. It’s very important in a sales company because it :

- Boosts employee motivation
- Increases target achievement
- Encourages healthy Competition
- Creates loyalty of employees to the company
- Reduces turnover of employees

Here we’ve got a dataset of the **Daily Sales List** of 7 **Sales Reps** and their consecutive amount of **Daily Sales**. Also, have a **Sales Hurdle** of $15,000 and a **Bonus Percentage** of 10%, which means they have to exceed the minimum limit of $15,000 sales target and for the remaining amount, they will be paid with a 10% bonus.

Now, we will calculate their sales bonus based on those criteria using a handful of different methods.

### 1. Using IF Function to Calculate Bonus in Excel

The first method we’re using here is using of the **IF function**. Using this function we can apply a logical test that sales reps with higher than $15,000 will get a 10% bonus and those who have sales lower than $15,000 won’t get any incentive. To do so, follow our steps below.

**Steps:**

- In the beginning, select cell
**D8**, type down the formula below, and press**ENTER**.

`=IF(C8>$C$4,(C8-$C$4)*$C$5,0)`

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

**Formula Breakdown**

With the **IF function**, we have applied a logical test that if the daily sales of any sales rep are greater than or equal to $15,000, he’ll get a 10% bonus on the remaining amount. Which is subtracting $15,000 from his daily sales. On the other hand, if he failed to achieve the target of $15,000, then he won’t be eligible for any bonus.

- Then, we used the
**Fill Handle**tool to get the remaining results of column**D**. Clearly, you can see that in the above image.

Similarly, you can do it in the case of false values. We are just giving it to increase your thinking capability. To look at something in alternative ways.

In our previous method, we applied logical tests to get bonuses in true values and those who weren’t applicable came through the false values. But, we will apply the opposite logic in this method. Here, those who aren’t eligible for a bonus come through true values, and others will come through true values. Let’s see the procedure as follow.

**Steps:**

- At first, select cell
**D8**as before, write down the formula below, and press**ENTER**.

`=IF(C8<$C$4,0,(C8-$C$4)*$C$5)`

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

**Read More:** **How to Calculate Commission in Excel Using IF Function**

### 2. Applying MAX Function to Calculate Bonus in Excel

Another tricky way to calculate bonuses in Excel is to apply the **MAX function**. Follow our work steps to calculate in this way.

**Steps:**

- Firstly, select cell
**D8**, paste down the formula as follows, and press**ENTER**.

`=MAX((C8-$C$4)*$C$5,0)`

**Formula Breakdown**

Here **MAX** function shows the maximum between the two numbers given as arguments. In our formula, **MAX((C8-$C$4)*$C$5,0) **the first number is the amount of **Bonus** which is before the comma, and the second number is zero. When the **Daily Sales** are greater than the **Sales Hurdle**, then the bonus becomes a positive number. Otherwise, the bonus becomes negative. So, between a positive number and zero, obviously, the positive number is bigger and as a result, it shows in the cell. In contrast, between a negative number and zero, zero is bigger, so then, it displays in the cell.

**Read More:** **How to Calculate Bonus on Salary in Excel (7 Suitable Methods)**

### 3. Engaging Boolean Logic

Another interesting way to calculate bonus in Excel is to use the Boolean logic in our formula. Go after the steps below we’ve done.

**Steps:**

- First, select cell
**D8**, enter the below formula into the**Formula Bar**, and then, press**ENTER**.

`=(C8-$C$4)*$C$5*(C8>=$C$4)`

**Formula Breakdown**

After **(C8-$C$4)*$C$5 **this part of the formula, we joined **(C8>=$C$4)** that with a multiplication sign. Easily we all can understand that the first part is for calculating the bonus amount. But *do you have any idea what the 2*^{nd}* part is for? *Let’s tell the secret. This 2^{nd }part is for applying Boolean logic to the formula. We know that, in Boolean algebra,

**True = 1**

**False = 0**

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.

**Read More:** **How to Calculate Sales Commission Formula in Excel (3 Easy Ways)**

### 4. Implementing VLOOKUP Function

The most effective and time-saving among our entire methods is to use the **VLOOKUP function**. Now, using this function we’ll solve our problem.

For this method, we made a slight change in our dataset. First, get a look into it.

Previously, we fixed our *sales hurdle* at $15,000 with a 10% bonus. But now we have added another limit of $10,000 with a *bonus percentage* of 5%. Besides, we decreased the *daily sales* amount of **Nick** and **Leon**.

Now, follow our footsteps as follows.

**Steps:**

- At the very beginning, select cell
**D10**, type down the formula below, and press**ENTER**.

`=VLOOKUP(C10,$B$5:$C$7,2)*(C10-$B$6)`

- Then, we used the
**Fill Handle**tool and by dragging it down got the other values of column**D**.

**Read More:** **How to Calculate Commission in Excel Using VLOOKUP**

### 5. Employing VBA Code to Calculate Bonus in Excel

A further way to solve this problem is to employ the **VBA** code. Applying **VBA** is always an amusing way. Follow our instructions carefully below.

**Steps:**

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

- Instantly, the Microsoft Visual Basic for Applications window opens. From
**Toggle Folders**, right-click on**Sheet7 (VBA)**> select**Insert**>**Module**.

- It opens a code module, where paste the code below. Then, click on the
**Run**button or press**F5**.

```
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
```

- Now close the code module and return to the worksheet. You will be amazed to see that 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.

## Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website **ExcelDemy** to explore more.