Stepped Fee Calculations with Max for each tier

[wazing]

I've looked around and need a quick formula to do a stepped fee calculation with a max amount for each tier and can't figure out how to use what I did find and add the max figure.

We process transactions and need a tool to calculate total fees. Here are the details:

Transaction Fees Per Order:
Total number of monthly orders 1-100 5% of the total dollar amount up to a max fee of $1.50
orders 101-199 5% up to $1.00
orders 200+ 5% up to $.50


Separate Total Group Orders (multiple payors for a single order, see attached spreadsheet):

Order initiator same fees as above
Invitee fees for group order: 50% of above fee totals

Attached is the tool we need the calculation in....any help is appreciated!

Reseller Commission Tool V1.xlsx

[FDibbins]

Is B5 what you want the calcs based on?

Aslo, could you include a sample answer, along with how you arrived at that please?

[wazing]

Any ideas?

Thanks!

[wazing]

Here is a revised worksheet with an example......any help?

Tool V2.xlsx

[wazing]

Yes...b5 is the total transactions and b6 is the value of each transaction for the formula.

So the first 100 transactions are charged at 5% of the transaction value (b6) up to a maximum fee of $1.5. Then the next 100 transactions are charged at 5% of the transaction value up to a maximum of $1.00...then any transactions over 200 are charged at 5% of the transaction value up to a maximum of $.5 per transaction (these fees are all per transaction).

So, a monthly total for 150 transactions with an average value of $20 per transaction would be:

Fixed fee of $25 per location (see sheet) + 100*$20*5% ($100 since the fee was less than $1.5 per transaction) + 50*$20*5% ($50 since the fee was right at $1 per transaction) = ($25+$100+$50=$175)

Hopefully that outlines the logic....