I am trying to write a small formula to calculate the bonus potential based on hitting my targeted sales parameters:
Salary.JPG
Depending on how much sales are exceeded by there is a corresponding chart that shows the payout potential:
table.JPG
All that needs to be done is input your Salary (B1) and your sales to plan (B9) and your bonus payout should be calculated in B12.
I have a basic IF Function that is using VLookup to reference the table, but I am having issues calculating the payout. Whatever is reference from column B on the payout worksheet would be multiplied by number in the calculator worksheet in cell (B6) in this scenario the $700 figure.
This is my formula/Function:
=IF(B9>=99%, VLOOKUP(B9,'Earn over Plan'!$A$3:$B$52,2,FALSE*Calculator!B6), FALSE)
I believe my issues lies in the "FALSE*Calculator!B6" portion of the VLOOKUP function. Where FALSE is the percentage value return: 101%, 102%, etc., etc., and then multiply that by the value in cell B6 to give me the correct dollar value.
What am I doing wrong? It's late so I'm tired, maybe it's a simple fix and I am just missing it. Any help would be most welcome. Thanks!
-M
Bookmarks