I am having trouble with what is turning out to be a pretty complex formula to come up with a percentage in a cell…..

Here is what I am looking at. This is for a commission compensation plan bonus field. The formula for how the person gets paid a bonus however, has a number of conditions. I am going to lay out the cells involved to see if anyone can shed some light on it for me. I am decent at excel but this formula would take me forever to decipher and I know there is an easier way than I am thinking.

In HUMAN language here is what I am trying to accomplish for a result in Cell M43

The Figures I am trying to come up with are bonus figures based on the following criteria.


No matter what… IF F43 is less than 90% then M43 is always 0

BUT…
IF F43 is >=90% the following needs to be calculated….

IF D43 = “BRONZE” and I43 is between 80 and 90 percent then M43=200
IF D43 = “BRONZE” and I43 is between 90 and 100 percent then M43=300
IF D43 = “BRONZE” and I43 is 100 percent or more then M43=400

IF D43 = “SILVER” and I43 is between 80 and 90 percent then M43=400
IF D43 = “SILVER” and I43 is between 90 and 100 percent then M43=600
IF D43 = “SILVER” and I43 is 100 percent or more then M43=800

IF D43 = “GOLD” and I43 is between 80 and 90 percent then M43=800
IF D43 = “GOLD” and I43 is between 90 and 100 percent then M43=1200
IF D43 = “GOLD” and I43 is 100 percent or more then M43=1600