|I came up with a way to calculate the bonuses, but not in a single formula. It may be possible to do it in a single formula, but the fact that you've got so many variables, it makes my head hurt to even think about it.|
What I did was create a table where you can put your target values, your bonus values, your bonus multipliers, etc.
I then created 2 formulae, one for Product A and one for Product B, which are on dependent on the percentage of each product sold. They will calculate the bonus multiplier for each product separately, but take into account the percentage sold of the other product.
I believe I was able to duplicate the results of the examples you gave.
One of the advantages of using a table is that you can change the targets or the bonus percentages or anything like that, and leave the formula alone.
I will post one of the formulae in this response, but it won't do you any good unless you see the layout of the tables. As much as I hate to post solutions off-list, it would be impossible to try and describe the layout of the spreadsheet in this forum.
If you'll PM me an email address, I'll send you the spreadsheet that uses the formula below: