Hello and thank you in advance for any help provided. I have a scenario where I pay out when units are greater than 0 at $2.00 per unit. However, I am adjusting that to pay out when units are greater than 3. How can I produce a new blended rate so that there is no change in total dollars? Essentially I want to bake the costs of the 0-3 units into the new rate so there is no change in costs. So in the below left table at 0-3 units I paid a total of $22. The table at the right for over 3 units I paid $150 for a total of $172. What formula or combination of will calculate a new blended rate to get me to total costs of $172 by using the units in the table on the right?
Units | Rate | $ | Units | Rate | $ | |
2 | $2.00 | $4.00 | 5 | $2.00 | $10.00 | |
3 | $2.00 | $6.00 | 10 | $2.00 | $20.00 | |
2 | $2.00 | $4.00 | 15 | $2.00 | $30.00 | |
1 | $2.00 | $2.00 | 20 | $2.00 | $40.00 | |
3 | $2.00 | $6.00 | 25 | $2.00 | $50.00 | |
11 | $22.00 | 75 | $150.00 |