YTD calculation based on different tiers

[politicalangel]

Hi,
I need help in creating a formula for the below situation.
Each region( about 30) in the report I am trying to create has different levels so if YTD amounts get to a certain level an additional amount is added..if it moves onto the next level the difference between the previous level and the new amount is calculated and the % awarded to the region.
I need a calculation that I can drag through from month to month for the regions that calculates the bonuses/amount when the levels are met for my accrual.

Please see attachment...

 

[ravi_i]

Hi,

The attached excel has some formulae that can help you with your requirement.

FYI-- I have changed the layout of the additional commission percentages in a manner that can be leveraged in a excel formula. I have ensured that every region has a bucket that begins with 1 and a bucket that ends with a very high number (eg. 10MM).

Inputs: Yellow cells
Intermediate: Gray cells
Final: Green cells

Please let me know if you have any further questions.

Thanks,
RI