I have a workbook where I input information in specific cells to create combinations to calculate employee bonuses. My workbook has 4 tabs:
The Data tab is formatted like this:
<tbody>
</tbody>
C1 IF formula calculates based on the combinations listed below:
Here are the different combinations numbered 1,2,3,5:
Employee % (cell B3) & Company % (cell B4) cells have numbers inputted = Combo 1
Employee $ (cell B5) & Company $ (cell B6) cells have numbers inputted =Combo 2
Base Salary (B2) is Yes & Employee % (cell B3) cells have been inputted = Combo 3
Base Salary (B2) is Yes & Job Title (A8) & Employee % (cell B8) cell have been inputted = Combo 5
I will further explain combo 3 specifically - If the base bonus is less than the regular bonus, than multiply the base bonus * Employee % + Company&Vendor bonus * Company %. If base bonus is more than regular bonus, than multiply regular bonus * Employee % + Company&Vendor bonus * Company %. Example:
Bobby
Base Bonus $50 < Regular Bonus $100
$50 * 50% employee % = $25
+ $200 company + $0 vendor * 50% company = $100
Final answer is $125 if base bonus is < regular bonus
I will further explain combo 5 specifically - If the base bonus is less than the regular bonus, than multiply the base bonus * employees job title Employee % + Company&Vendor bonus * job title Company %. If base bonus is more than regular bonus, than multiply regular bonus * employees job title Employee % + Company&Vendor bonus * job title Company %. Example:
Bobby
Base Bonus $50 < Regular Bonus $100
Find Bobby's Job title to determine Employee % & Company %
75% Employee % for Officers and 50% Company %
75% * $50 + 50% * ($200 Company & $0 Vendor) = $137.5
Final answer is $137.5 of base bonus is < regular bonus
---------------------------------------------------------------------------------------------------------------------------------
The Bonus Tab is formatted like this:
<tbody>
</tbody>
-----------------------------------------------------------------------------------------------------------------------------------
The Base Bonus Tab is formatted like this:
<tbody>
</tbody>-----------------------------------------------------------------------------------------------------------------------------------
The Summary Tab is formatted like this:
<tbody>
</tbody>-----------------------------------------------------------------------------------------------------------------------------------
The issue I am having is when I combine all of the formulas together, I receive the Too Many Arguments error message. I would like to avoid making a secondary calculation table, but I am open to resolution options. Here are the formulas by combination that I would like to see if they can be put together into one large formula. See Next Comment for formulas.
- Data
- Bonus
- Base Bonus
- Summary
The Data tab is formatted like this:
| Column A | Column B | Column C |
| IF formula below | ||
| Base Salary? | ||
| Employee % | ||
| Company % | ||
| Employee $ | ||
| Company $ | ||
| Job Title | Employee % | Company % |
| Employee | Job Title | |
| Bobby | Officer | |
| Ricky | Officer | |
| Tiesto | Officer | |
| Robert | Officer |
<tbody>
</tbody>
C1 IF formula calculates based on the combinations listed below:
Code:
=IF(AND($B$2<>"",$A$8<>"",$B$8<>""),5,IF(AND($B$2<>"",$B$3<>""),3,IF($B$3<>"",1,IF($B$5<>"",2))))Here are the different combinations numbered 1,2,3,5:
Employee % (cell B3) & Company % (cell B4) cells have numbers inputted = Combo 1
Employee $ (cell B5) & Company $ (cell B6) cells have numbers inputted =Combo 2
Base Salary (B2) is Yes & Employee % (cell B3) cells have been inputted = Combo 3
Base Salary (B2) is Yes & Job Title (A8) & Employee % (cell B8) cell have been inputted = Combo 5
I will further explain combo 3 specifically - If the base bonus is less than the regular bonus, than multiply the base bonus * Employee % + Company&Vendor bonus * Company %. If base bonus is more than regular bonus, than multiply regular bonus * Employee % + Company&Vendor bonus * Company %. Example:
Bobby
Base Bonus $50 < Regular Bonus $100
$50 * 50% employee % = $25
+ $200 company + $0 vendor * 50% company = $100
Final answer is $125 if base bonus is < regular bonus
I will further explain combo 5 specifically - If the base bonus is less than the regular bonus, than multiply the base bonus * employees job title Employee % + Company&Vendor bonus * job title Company %. If base bonus is more than regular bonus, than multiply regular bonus * employees job title Employee % + Company&Vendor bonus * job title Company %. Example:
Bobby
Base Bonus $50 < Regular Bonus $100
Find Bobby's Job title to determine Employee % & Company %
75% Employee % for Officers and 50% Company %
75% * $50 + 50% * ($200 Company & $0 Vendor) = $137.5
Final answer is $137.5 of base bonus is < regular bonus
---------------------------------------------------------------------------------------------------------------------------------
The Bonus Tab is formatted like this:
| Column A | Column B | Column C | Column D | Column E | Column F | Column G |
| Name | Yrs. of Service | Promotions | Bonus | Company | Vendor | Total |
| Bobby | 0 | 0 | 100 | 200 | 0 | 300 |
| Ricky | 10 | 1 | 100 | 200 | 100 | 400 |
| Tiesto | 0 | 0 | 250 | 500 | 0 | 750 |
| Robert | 3 | 1 | 300 | 600 | 0 | 900 |
<tbody>
</tbody>
-----------------------------------------------------------------------------------------------------------------------------------
The Base Bonus Tab is formatted like this:
| Column A | Column B |
| Name | Base Bonus |
| Bobby | 50 |
| Ricky | 100 |
| Tiesto | 300 |
| Robert | 300 |
<tbody>
</tbody>
The Summary Tab is formatted like this:
| Column A | Column B |
| Name | Total Bonus |
| Bobby | Formulas based on combination |
| Ricky | Formulas based on combination |
| Tiesto | Formulas based on combination |
| Robert | Formulas based on combination |
<tbody>
</tbody>
The issue I am having is when I combine all of the formulas together, I receive the Too Many Arguments error message. I would like to avoid making a secondary calculation table, but I am open to resolution options. Here are the formulas by combination that I would like to see if they can be put together into one large formula. See Next Comment for formulas.
