Calculate total of tiered payment
Hi all, is there a way to break a number down to make multiple calculations? For example, I am going to pay my sales team based on the number of units they sell. The first 50 units are paid at $20/unit, next 25 units are paid at $30/unit, etc.
In the photo below I want it to automatically calculate the total ($3,250) simply by taking the number of units (110 for Person A) and multiplying it according to the table below it.
Thanks for any help!
Best Answer

Hi @Brad Mulder ,
Try this
=IF(Units@row<=50, units@row*20, IF(Units@row<=75, ((units@row50)*30)+1000, IF(UNITS@ROW<=100, ((units@row75)*30)+1750, ((units@row100)*50)+3000)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers

Hi @Brad Mulder ,
Try this
=IF(Units@row<=50, units@row*20, IF(Units@row<=75, ((units@row50)*30)+1000, IF(UNITS@ROW<=100, ((units@row75)*30)+1750, ((units@row100)*50)+3000)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Thanks Mark, that did work as hoped! Much appreciated!
Help Article Resources
Categories
Check out the Formula Handbook template!