Different royalty rate depending in unit sales

I need to calculate a royalty rate due which is based upon a Unit Price * Unit Sales. The royalty rate due changes at certain levels of sales.
I've attached sheet to hopefully make clear.
Many thanks
Dk

Hi,

See if this UDF helps u. I have assumed that above 750000 unit royalty paid @ 9% . See attached file. Find Function called 'Royalty' in User Defined Function. See green cells in file.

Thank you for this solution. How do I get to the User defined function so that I can change the rates / levels
Dk

Hi

U can see and change UDF by following steps

Select 'Tools' from Menu
Then 'macro' -> Visual basic editor
in Module1 u will see UDF

or u can open Visual Basic by pressing Alt+F11

Special K

Don't quite get the result I was expecting with your formula. At 10 unit price and 1000000 units I should get 575,000 royalty - but I get 825,000 with your formula - unless I've copied it incorrectly if so apologies in advance
Dk

Real newbie to this.
How do I move this macro from the royalty worksheet into another workbook that I need to use it in.
Dk

Hi

copying UDF

Open excel file in which u want this UDF, then press Alt+F11 to open Visual Basic. Insert Module .Then copy contents of Modul1 in original file and paste it in Module1 of new file.