Help with Commission Calculation based on accelerator and attainment tier

Hi there,

Need some help figuring out if there is a way to automate this calculation using a single formula in Excel.

Currently I'm using vlookup to use the accelerator tied to the attainment range, however this doesn't work when a sales rep crosses over the next attainment bucket with their current sale. I then need to do an override by manually calculating for each sales rep when there is a cross-over in tier, which is inefficient.

Example:
0%-30% Quota attainment = 0.5x accelerator to the base rate
31%-40% Quota attainment = 0.7x accelerator to the base rate

If a sales rep is at 29.9% attainment, and their next sale puts them at 30.2% attainment, I need to blend the accelerator of 0.5 + 0.7 for that specific new sale that crossed them over to the next tier. My current formula is putting them at 0.7x accelerator which is inaccurate, and after the override, it shows 0.62x as the correct accelerator.

Thank you!