I need to create a formula for the following (don't worry about the currency at this stage):
• 4% of the slice of the average monthly payment volume up to €5 million;
• 2.5% of the slice of the average monthly payment volume above €5 million up to €10
million;
• 1% of the slice of the average monthly payment volume above €10 million up to
€100 million;
• 0.5% of the slice of the average monthly payment volume above €100 million up to
€250 million; and
• 0.25% of the average monthly payment volume above €250 million.
To illustrate this:
If I input a value of 12 million, this would require a calculation of 4% of 5 million, 2.5% of 5 million, 1% of 2 million.
I am at a crossroads with how to prevent the 2.5% calculation going above the 10 million threshold and so on.
Any help would be greatly appreciated thank you.
Bookmarks