Formula to calculate royalty as sales level changes

Hi,

Sorry I couldnt think of the best way to title this!

So I am looking for some help with creating a spreadsheet where I can manually change sales level thresholds and royalty rates which will in turn automatically update.

Company A pays a royalty to Company B at a royalty rate based on sales levels. So on the spreadsheet it's the first 16,000,000 at 3%, the next 5,999,999 at 4%, the next 9,999,999 at 5% and so on. I need to figure out the way to link this so that I can check the royalty due based on several different varying factors;

Change of threshold level
Change of royalty rate
Change of NET sales

So based on the spreadsheet and NET sales of 49,000,000, the royalty threshold goes into the 8% bracket. However if I wanted to change the sales thresholds to say go up in 10m increments then right now I would need to manually come into this and change the formulas. Ideally I would change the "to" cells and that would automatically update the sales level at which royalty is due. As you can see if I was to do that currently there would be a -1,000,000 in cell D7 but I would like it to show a 0 and in D6 show 9,000,000 due at 7%.

Does anyone know what formula I would need to put into Column D to try to make this work?

Thanks for any help in advance, and very sorry I probably havent explained this very well!

Steve

Hi, yes this has worked perfectly, thanks so much!