This is a very, very simplified calculation based on a cubic equation that has turning points (1,0) and (0,1), which gives a curve whose slope tapers at the maximum and minimum (you can use a higher-order equation for greater control over the shape of the curve or use a logarithmic function for a different type of curve altogether). I’ve chosen (1,0) and (0,1) to work with the decimal form of the percentages, but you can use y=.0002x^3-.03x^2+100 if you’re working with Airtable’s interger percentages, which range from 0 to 100.
The key variable you need to do this sort of calculation is {Remaining Percentage of Days}, which is derived from ({Event Date} - TODAY) / ({Event Date} - {Ticket On Sale Date}). I’ve created a helper field with this number to improve the legibility of the formula, since we’re using this variable multiple times.
The equation I’m using is y = 2x^3 - 3x^2 + 1
https://www.google.ca/search?q=y%3D2x^3-3x^2%2B1
Translated to an Airtable formula, it would look like this:
{Total Tickets} * (2 * POWER({Remaining Percentage of Days},3) - 3 * POWER({Remaining Percentage of Days},2) + 1)
Here’s a sample base of the formula in action:
There’s an additional field at the end that applies the curve only for the last week, with the following formula. It’s similar to the above, but changes {Remaining Percentage of Days} to {Days Until Event}/7.
IF({Days Until Event}>6,‘Minimal’,INT({Total Tickets}(2POWER({Days Until Event}/7,3)-3*POWER({Days Until Event}/7,2)+1)))
You can of course combine curves using an IF formula, or start the final week curve at a higher base ticket count.
Unfortunately, I don’t have a stats background so I can’t tell what type of function would work best for you (also, Airtable is missing some of the math functions available in Excel, so you may not necessarily be able to apply the functions you find).
