When you maintain a company, you have to give some bonus to your employees to make them more dedicated to your company. It is a trendy policy in every country. Employees who donâ€™t feel good about their salaries must have lost motivation to work harder. To motivate them, you need to calculate the bonus as per the bonus act in Excel. In Microsoft Excel, you have a platform where you can easily calculate bonuses. This article will show every detail of bonus calculation as per the bonus act in Excel. I hope you find it really helpful and informative for your future purposes.

**Table of Contents**Expand

## Overview of Bonus Calculation

The bonus can be defined as a reward that is actually paid to the employee to have better dedication towards the work. These rewards can improve the productivity of a certain employee. To calculate a bonus for any company, you need to set a standard salary which is the average salary of this company. Let, the average salary of any USA-based company is **$5500**. To calculate the bonus, we have two different approaches.

- If the salary is less than
**$5500**, the bonus will be calculated using their actual basic salary. For example, if someone gets**$3500**, his bonus will calculate using this**$3500**. As per the bonus act, the bonus will be**33%**of the basic salary.

**Formula:**

**Bonus = Basic Salary * 8.33%**

**Â Â Â Â Â Â = $3500 * 8.33% **

**Â Â Â Â Â Â = $291.55**

- Then, if the salary is greater or equal to
**$5500**, the bonus will be calculated using the basic salary ofÂ**$5500.**For example, if someone gets $7000, his bonus will be calculated using a basic salary ofÂ**$5500**.

**Formula:**

**Bonus = Basic Salary * 8.33%**

**Â Â Â Â Â Â = $5500 * 8.33% **

**Â Â Â Â Â Â = $458.15**

## Bonus Calculation as per Bonus Act in Excel: 3 Suitable Examples

To calculate the bonus as per the bonus act in Excel, we have found three different approaches through which you can easily calculate the bonus. To calculate bonus as per bonus in Excel, we can use multiple**Â **functions which we will show you in the upcoming sections. Before using any function to calculate a bonus, we need to set the salary range and corresponding bonus. That means, if the salary is in that range, the employee will get that salary. To show all the methods effectively, we take a dataset that includes some employeesâ€™ names, ID No., and their basic salaries.

### 1. Using IFS Function

Our first method is based on** the IFS function**. This function is mainly applicable when you have multiple conditions. To use this function effectively, follow the following steps carefully.

**Steps**

- At first, you need to set a salary range.
- From that salary range, calculate the bonus.
- To calculate the bonus, we take the lower salary. That means for the
**$3000-$3499**range, select**$3000**as the lower range. - Then, select cell
**D5**.

- Write down the following formula in the formula box.

`=C5*8.33%`

- Press
**Enter**to apply the formula.

- Then, drag the formula down the column to cell
**D9**.

- When the basic salary is greater or equal to
**$5500**, we always take the basic salary of**$5500**to calculate the bonus. Our last four salary ranges are above**$5500**or equal, in that case, we use a basic salary of**$5500**. - Select cell
**D10**.

- Then, write down the following formula in the formula box.

`=$C$10*8.33%`

- Press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the cell**C13**.

- Then, to calculate the bonus of our dataset, select cell
**E5**.

- Write down the following formula in the formula box.

`=IFS(D5<3500,Bonus!$D$5,D5<4000,Bonus!$D$6,D5<4500,Bonus!$D$7,D5<5000,Bonus!$D$8,D5<5500,Bonus!$D$9,D5<6000,Bonus!$D$10,D5<6500,Bonus!$D$11,D5<7000,Bonus!$D$12,D5>=7000,Bonus!$D$13)`

- Then, press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the column. It will automatically select the bonus for that salary by utilizing the formula we apply.

- Finally, we want to calculate the overall amount the employee will get after the bonus.
- For that, at first, select cell
**F5**.

- Then we use
**the SUM function**to calculate the overall salary. - Write down the following formula.

`=SUM(D5:E5)`

- Press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column. - This will be our required bonus calculation as per the bonus act in Excel using the
**IFS**function.

**ðŸ”Ž**** Breakdown of the Formula**

**IFS(D5<3500,Bonus!$D$5,D5<4000,Bonus!$D$6,D5<4500,Bonus!$D$7,D5<5000,Bonus!$D$8,D5<5500,Bonus!$D$9,D5<6000,Bonus!$D$10,D5<6500,Bonus!$D$11,D5<7000,Bonus!$D$12,D5>=7000,Bonus!$D$13)**

**â‡’ IFS(D5<3500,Bonus!$D$5 â€¦â€¦): **This formula denotes that if cell **D5** is less than **$3500**, then, it will go to the sheet called **Bonus** and returns the value of cell **D5** of that Bonus sheet. That means if the salary is less than **$3500**, it will go to the **Bonus **sheet and return with the value of cell **D5 **which is the bonus of that range of salaries. If this condition doesnâ€™t meet, it will go to the next condition.

**â‡’ IFS(D5<3500,Bonus!$D$5,D5<4000,Bonus!$D$6 â€¦â€¦): **If the cell **D5** is not less than **$3500** then it will go to the next condition where it checks if the cell **D5** is less **$4000** then, it will go to the sheet called **Bonus** and returns the value of cell **D5** of that Bonus sheet. That means if the salary is less than **$4000**, it will go to the **Bonus **sheet and return with the value of cell **D5 **which is the bonus of that range of salaries. If this condition doesnâ€™t meet, it will go to the third condition.

**â‡’ **The cell **D5** will go through all the conditions until it meets the condition. When it meets the condition, it will stop in there and returns the bonus value from the **Bonus** sheet.

### 2. Applying VLOOKUP Function

Our next method is based on** the VLOOKUP function**. This function is mainly used to search the value across the column. When you use a large dataset or multiple datasets, in that case, to search some values across the column, we use the **VLOOKUP** function. To calculate bonus as per bonus act in Excel using the **VLOOKUP** function, follow the steps carefully.

**Steps**

- At first, you need to set a salary range.
- From that salary range, calculate the bonus.
- To calculate the bonus, we take the lower salary. That means for the
**$3000-$3499**range, select**$3000**as the lower range. - Then, select cell
**D5**.

- Write down the following formula in the formula box.

`=C5*8.33%`

- Press
**Enter**to apply the formula.

- Then, drag the formula down the column to cell
**D9**.

- When the basic salary is greater or equal to
**$5500**, we always take the basic salary of**$5500**to calculate the bonus. Our last four salary ranges are above**$5500**or equal, in that case, we use a basic salary of**$5500**. - Select cell
**D10**.

- Then, write down the following formula in the formula box.

`=$C$10*8.33%`

- Press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the cell**C13**.

- To calculate the bonus using the
**VLOOKUP**function, select cell**E5**.

- Write down the following formula in the formula box.

`=VLOOKUP(D5,Bonus!C$5:D$13,2,TRUE)`

- Then, press
**Enter**to apply the formula.

- After that, drag the Fill handle icon down the column. It will automatically select the bonus for that salary by utilizing the formula we apply.

- Finally, we want to calculate the overall amount the employee will get after the bonus.
- For that, at first, select cell
**F5**.

- Then we use the
**SUM**function to calculate the overall salary. - Write down the following formula.

`=SUM(D5:E5)`

- Press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column. - This will be our required bonus calculation as per the bonus act in Excel using the
**VLOOKUPÂ**function.

**ðŸ”Ž**** Breakdown of the Formula**

**VLOOKUP(D5,Bonus!C$5:D$13,2,TRUE)**

This formula denotes that at first, it searches cell **D5**. Then it goes to the sheet called Bonus and goes through the whole range of cells from **C5** to **D13**. After that, it moves to column **2 (Col_index_num) **where they find out the approximate match which is why we use True for an approximate match. The function returns the value for which it matches the **Bonus** worksheet range of cells. Here, it returns the bonus of **$333.2**.

### 3. Use of LOOKUP Function

Our last method is based on** the LOOKUP function**. This function also provides the same output as the** VLOOKUP **function but in a different way. To calculate the bonus as per the bonus act in Excel using the **LOOKUP** function, you need to follow the steps carefully.

**Steps**

- At first, you need to set a salary range.
- From that salary range, calculate the bonus.
- To calculate the bonus, we take the lower salary. That means for the
**$3000-$3499**range, select**$3000**as the lower range. - Then, select cell
**D5**.

- Write down the following formula in the formula box.

`=C5*8.33%`

- Press
**Enter**to apply the formula.

- Then, drag the formula down the column to cell
**D9**.

- When the basic salary is greater or equal to
**$5500**, we always take the basic salary of**$5500**to calculate the bonus. Our last four salary ranges are above**$5500**or equal, in that case, we use a basic salary of**$5500**. - Select cell
**D10**.

- Then, write down the following formula in the formula box.

`=$C$10*8.33%`

- Press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the cell**C13**.

- To calculate the bonus using the
**LOOKUP**function, select cell**E5**.

- Write down the following formula in the formula box.

`=LOOKUP(D5,Bonus!$C$5:$C$13,Bonus!$D$5:$D$13)`

- Then, press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the column. It will automatically select the bonus for that salary by utilizing the formula we apply.

- Finally, we want to calculate the overall amount the employee will get after the bonus.
- For that, at first, select cell
**F5**.

- Then we use the
**SUM**function to calculate the overall salary. - Write down the following formula.

`=SUM(D5:E5)`

- Press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon down the column. - This will be our required bonus calculation as per the bonus act in Excel using the
**LOOKUPÂ**function.

**ðŸ”Ž**** Breakdown of the Formula**

**LOOKUP(D5,Bonus!$C$5:$C$13,Bonus!$D$5:$D$13) **

This function demonstrates that at first, it will look up cell **D5. **Then, go through the range of cells from the Bonus worksheet and find out possible matches with cell **D5**. If it gets any match then, it returns the value corresponding to cell **D5**. Here, it returns** $333.2** for the salary of **$4000**.

**Download Practice Workbook**

Download this practice workbook.

## Conclusion

We have shown three different and effective methods to show bonus calculation as per the bonus act in Excel. These methods include several Excel functions. Here, we have taken a range of salaries. Within these ranges, bonus changes. All the processes are fairly easy and applicable for your day-to-day purposes. IÂ hope you find this article very informative and gain some valuable knowledge regarding this issue.

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