tkessler,
Attached is an example workbook based on the sample data you provided. It contains a helper table in columns E:F that has the Min - Max threshold ranges, what the base pay is and the + per Mil rate is for each threshold. Column A contains random numbers between 4 million and 50 million. Column B contains the formula:
=INDEX($G$2:$G$4,MATCH(A2,$E$2:$E$4,1))+INDEX($H$2:$H$4,MATCH(A2,$E$2:$E$4,1))*INT((A2-INDEX($E$2:$E$4,MATCH(A2,$E$2:$E$4,1)))/1000000)
Adjust the upper bounds as necessary as you increase the helper table.
Hope that helps,
~tigeravatar
Bookmarks