Hello Again! This is a continuation to my most recent post regarding this bonus structure discussed below. Here's an overview of what I'd like to achieve :
I am calculating the bonus % achieved based on a sliding scale of gross margin for a project with 2 types of bonus categories. The slide scale is different for each category as shown below and a % of the total revenue is awarded based on the gross margin falling within the scales :
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Building Projects[/TD]
[TD]22.9% & Under[/TD]
[TD]23.0%-26.9%[/TD]
[TD]27.0% & Over[/TD]
[/TR]
[TR]
[TD]% of Total Revenue[/TD]
[TD]0.00%[/TD]
[TD]0.25%[/TD]
[TD]0.50%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Emergency Projects[/TD]
[TD]47.9% & Under[/TD]
[TD]48.0%-50.9%[/TD]
[TD]51% & Over[/TD]
[/TR]
[TR]
[TD]% of Total Revenue[/TD]
[TD]0.00%[/TD]
[TD]0.25%[/TD]
[TD]0.50%[/TD]
[/TR]
</tbody>[/TABLE]
I would to like to generate the above in a nested If AND statement with the data presented below :
F13 = Building or Emergency
E13 = Gross Margin % Achieved
Here is the formula :
=IF(F13="Building",IF(AND(E13<=22.9%),0%,IF(AND(E13>=23%,E13<=22.9%),0.07%,IF(AND(E13>=27%),0.1%,0,IF(F13="Emergency",IF(AND(E13<=47.9%),0%,IF(AND(E13>=48.0%,E13<=50.9%),0.25%,IF(AND(E13>=51%),0.50%,0))))))))
I am using Excel 2016 Version
Values are not being returned, but instead the following message is produced : "You've entered too many arguments for this function."
Here's the image of the spreadsheet :
Thanks in advance and looking forward to the reply!
MAKlos
I am calculating the bonus % achieved based on a sliding scale of gross margin for a project with 2 types of bonus categories. The slide scale is different for each category as shown below and a % of the total revenue is awarded based on the gross margin falling within the scales :
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Building Projects[/TD]
[TD]22.9% & Under[/TD]
[TD]23.0%-26.9%[/TD]
[TD]27.0% & Over[/TD]
[/TR]
[TR]
[TD]% of Total Revenue[/TD]
[TD]0.00%[/TD]
[TD]0.25%[/TD]
[TD]0.50%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Emergency Projects[/TD]
[TD]47.9% & Under[/TD]
[TD]48.0%-50.9%[/TD]
[TD]51% & Over[/TD]
[/TR]
[TR]
[TD]% of Total Revenue[/TD]
[TD]0.00%[/TD]
[TD]0.25%[/TD]
[TD]0.50%[/TD]
[/TR]
</tbody>[/TABLE]
I would to like to generate the above in a nested If AND statement with the data presented below :
F13 = Building or Emergency
E13 = Gross Margin % Achieved
Here is the formula :
=IF(F13="Building",IF(AND(E13<=22.9%),0%,IF(AND(E13>=23%,E13<=22.9%),0.07%,IF(AND(E13>=27%),0.1%,0,IF(F13="Emergency",IF(AND(E13<=47.9%),0%,IF(AND(E13>=48.0%,E13<=50.9%),0.25%,IF(AND(E13>=51%),0.50%,0))))))))
I am using Excel 2016 Version
Values are not being returned, but instead the following message is produced : "You've entered too many arguments for this function."
Here's the image of the spreadsheet :

Thanks in advance and looking forward to the reply!
MAKlos