Calculate total of tiered payment

Brad Mulder

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!

Mark Cronk

Hi @Brad Mulder ,

Try this

=IF(Units@row<=50, units@row*20, IF(Units@row<=75, ((units@row-50)*30)+1000, IF(UNITS@ROW<=100, ((units@row-75)*30)+1750, ((units@row-100)*50)+3000)

Work?

Mark

Mark Cronk

Hi @Brad Mulder ,

Try this

=IF(Units@row<=50, units@row*20, IF(Units@row<=75, ((units@row-50)*30)+1000, IF(UNITS@ROW<=100, ((units@row-75)*30)+1750, ((units@row-100)*50)+3000)

Work?

Mark

Brad Mulder

Thanks Mark, that did work as hoped! Much appreciated!