Creating Airtable formulas to calculate auction house commission rates

I am looking for some help please to bring a formula from Excel into Airtable in order to calculate Auction fees and commission based on certain variables.

First, I will explain how this example auction works:

The Hammer Price is the final amount someone bids on an item. The auction adds a “Buyers Premium” based on the final amount to be paid by the buyer. Here is how it is calculated.

X Auction charges a Buyer’s Premium calculated on the Hammer Price as follows: a rate of twenty-five percent (25%) of the Hammer Price of the Lot up to and including $25,000; plus twenty percent (20%) on the part of the Hammer Price over $25,000 and up to and including $5,000,000; plus fifteen percent (15%) on the part of the Hammer Price over $5,000,000.

The eventual buyer pays the Hammer Price + the Buyer’s Premium.

In addition to the above charges, Auction X also charges the seller a fixed Selling Commission of 10% on the Hammer Price. This is amount is subtracted from the Hammer Price before the proceeds are remitted to the seller. Therefore, the total auction commission is the Selling Commission + Buyer’s Premium.

If I know the Hammer Price, I can easily calculate the Buyer’s Premium with the attached Array Formula in Excel. I am looking for some help please to bring this formula into Airtable.

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(S4>S7:S10,(IF(S4<S8:S11,S4,S8:S11)-S7:S10)*T8:T11,0))), 1, 1)

If I don’t know the Hammer Price, and perhaps only what the buyer paid, I would like to be able to reverse calculate the Hammer Price and what the seller received using the same variables described above.

I would really appreciate some help producing the various formulas in Airtable.

Thank you!

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.