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 :
<tbody>
</tbody>
<tbody>
</tbody>
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 :
Building Projects | 22.9% & Under | 23.0%-26.9% | 27.0% & Over |
% of Total Revenue | 0.00% | 0.25% | 0.50% |
<tbody>
</tbody>
Emergency Projects | 47.9% & Under | 48.0%-50.9% | 51% & Over |
% of Total Revenue | 0.00% | 0.25% | 0.50% |
<tbody>
</tbody>
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