I have a workbook where I calculate bonus by employee based on various combinations inputted into specific cells. I will do my best to describe each combinations:
Cell C1: IF formula for the specific 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
Based on which combo is inputted dictates which formula is used to calculate the bonus.
I will further explain combo 3 - 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 - 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 problem I am having is combo 3 & 5 both use the false statement within the nested IF formula causing the "Too Many Arguments" error message when I try to combine all of the formulas together. Within the attached spreadsheet, I labeled the formula for each combo, but I am trying to combine all of the formulas together into one long IF formula rather than separate formulas. I am trying to avoid creating a secondary table for calculations if possible, but open to ideas.
Thank you for the help!
Bookmarks