Multiple Condition Formula to calculate a final figure

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

My take, not fully tested

Formula:

Please Login or Register  to view this content.

How about a hybrid (not fully tested)

Formula:

Please Login or Register  to view this content.

@jeff... Good question. The 90 is in a different cell. They have to reach 90 I'm that cells metrics to qualify for the bonus structure that the other cell is calculating. Basically. If they don't hit 90 percent of their individual goal than they don't qualify for the store manager bonuses

@Cutter and Jeff. This works, the only thing I am trying to figure out is, if the item in I43 is LESS than .8, I would like it to return a zero rather than a #N/A Thanks

I figured that one out! Thanks for all your help, both of you!!

This solution works!