Tiered Buyer's Fee...With a Twist

[jeeperv6]

Hi all,

I've been trying to figure out a tiered buyer's fee for an auction company. I've been able to do some of if with a simple Lookup table. But there's a twist to it. I've uploaded my worksheet and highlighted in red where I can't seem to figure it out based on my existing formula.

The auction company's fees are:

1) 10% on all Lots selling for USD $5,000 or less
2) 3.85% on all Lots selling for over USD $5,000 up to USD $33,500, with a minimum fee of USD $500 per Lot or
3) USD $1,290 on all Lots selling for over USD $33,500

#1 & #3 are a piece of cake with the Lookup table. It's #2 that is causing me to scratch my head between a certain value range, where the minimum dollar value is required over the percentage.

I've searched over this website looking at previous posts about tiered calculations and haven't found one like this to use for an example.

Any help or guidance would be much appreciated!

[FlameRetired]

Please try in C4 and fill down.

Please Login or Register  to view this content.

[jeeperv6]

That works perfectly up to 33,000.00. After that it's a flat rate of 1290.00 as per #3 in the fee schedule. Could I mix in your Max formula into an If statement? IE, use your Max formula up to 33,500 and then just apply the max fee of 1290.00?

[FlameRetired]

That sounds like it should work. Give it a go and let's see.

[jeeperv6]

Adding the If statement to your formula to account for over 33,500.00 worked perfectly. Tried it from A4 to A49 (where I currently end) and it is a 100% throughout the range.

Thank you for your time & trouble.

[FlameRetired]

You are welcome. Glad it worked. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.