Apply formulas across summary bars

[Aaron_Vreeland]

The group nesting and sum features in Airtable are slick and potentially very useful, but not being able to display a weighted average in the groups’ summary bars is really painful. The solution to this problem appears, at least to me, to be very simple: Just implement an option in the summary bar pulldown of formula columns to apply their formula across the summary bar.

The existence of this feature will probably make or break whether our company will use Airtable.

[Kamille_Parks]

Agreed! Similarly, I have a table wherein the summary line has “NaN” the value for what should be “Average Event Rating” because it’s trying to include responses to events that haven’t happened yet (therefore no one has given the event a rating). If we could specify to only average non NaN values this would solve our issue.

[Elias_Gomez_Sai]

Aaron_Vreeland:

apply their formula across the summary bar

You mean an average? You have the results of the records’ formula, and then you can do some things with those numbers: average, sum, max… If you want the average, fix the formula so always gives you a number and not ‘Infinity’. You could use IF(Results, Formula) to avoid it.

[Aaron_Vreeland]

That’s hardly a solution. All it would do is give me a bad number instead of no number at all.

[Aaron_Vreeland]

You could create another view with a filter that excludes unranked events. Only problem is that the unranked events wouldn’t appear when you expand the group out.

[Kamille_Parks]

True, but I’d still like to get rid of Nan display in the views where I’d need to view all events at once. Plus others may find the original idea useful for more advanced use cases.

[Elias_Gomez_Sai]

What do you mean with bad number? It works:

[Aaron_Vreeland]

The result in your example should be $15,341 / 7 = $2,191.57
$1,058.58 is not even close. That’s why it’s a bad number.

I’m not looking for a simple average. I’m looking at a total spent for an advertisement campaign vs. the results it produced. If a particular ad produced zero results, I don’t want to just ignore the fact that money was spent on it (which is what your method does).

[Jeremy_Oglesby]

I think what you’re looking to do needs to be done in a separate table. The summary bar has to act on the column – it doesn’t reference “other summary bar formulas”, it reference the cells it encompasses in its column. I don’t see an easy way for Airtable to add what you’re looking for programmatically.

However, if you link each record to a “Summary” table, where each record in the Summary table represents an Ad Campaign, and each record in your other table that links to it represents itemized expenditures – you can use the Summary table to get the averages you’re looking for by using lookups/rollups.

Let me know if you’d like further explanation of that.

[W_Vann_Hall]

What you are attempting to do is a multi- or cross-record calculation. Roughly speaking, they come in two flavors: Complex ones, in which a formula in one record needs to access a value from another record offset from it by a specific number of days or sequential records, and [relatively] simple ones, where each record in a table needs to access some sort of summation of values for the entire table, be that an actual summation, an average, a minimum/maximum value, or the like.

How to handle the more complex version is explained here. The simpler version, as @Jeremy_Oglesby mentioned, requires

1. Linking every record of your main table to a single record in a second [Calc] table (here are instructions on how to do just that).
2. Creating a rollup field in the new table to provide whatever summary of the main table is required.
3. Retrieving the summary value from the [Calc] table via a lookup or rollup field in your [Main] table.

Aside from the all-to-one linkage, it’s reasonably straightforward; if you run into any problems, hit Jeremy or me up for help!

[W_Vann_Hall]

Is it just your summary line that displays 'NaN', or do the individual records being summarized display that as well? I’m guessing it’s the latter, which means the field you’re summarizing is a calculated field. If that’s the case, you can wrap the field with an IF() statement that causes only formulas for which the necessary values exist to be calculated.

For instance, if your event rating formula is something along the lines of TotalRating/NbrOfEvents and as a result evaluates as 'NaN' until at least one event has occurred, changing the formula to IF({NbrOfEvents},TotalRating/NbrOfEvents) will cause the field to return BLANK() in all instances where {NbrOfEvents} = 0. This will eliminate per-record 'NaN's and clear up the summary line, as well.

If you’re receiving a 'NaN' only in your summary line, create a new topic in ‘Ask the community’ with a little more detail as to what you are seeing, and we’ll try to figure it out, as that’s behavior I, at least, have never seen.