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.