Nested If AND Statement producing message with Too Many Arguments - Need alternative

[MAKlos]

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 :

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​

 

[Scott T]

The zero in red is the false argument for the IF statement so excel does not know what to do with everything after it.

[COLOR=#333333]=IF(F13="Building",IF(AND(E13<=22.9%),0%,IF(AND(E13>=23%,E13<=22.9%),0.07%,IF(AND(E13>=27%),0.1%,[/COLOR][COLOR=#ff0000]0[/COLOR][COLOR=#333333],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))))))))[/COLOR]

Maybe this is what you want. the percentages you have for building do not match what is in your table.

=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%,E13<=50.9%),0.25%,IF(AND(E13>=51%),0.5%,0)))))

 

[MAKlos]

Thank you Scott! I was racking my brain thinking I had something wrong with the percentages and Yes, I will correct the gross margin percentages to match the table. Thank you for catching that!

 

[AhoyNC]

Maybe a shorter option.

Excel Workbook
	E	F	G	H
13	49%	Emergency		0.0025
Sheet

 

[MAKlos]

Thank you AhoyNC! Appreciate the different look/perspective to simplify. Appreciate it!