Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Apply formulas across summary bars

cancel
Showing results for 
Search instead for 
Did you mean: 
Aaron_Vreeland
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

cVC48C0.png

11 Comments
Kamille_Parks
16 - Uranus
16 - Uranus

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
13 - Mars
13 - Mars

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Aaron_Vreeland
5 - Automation Enthusiast
5 - Automation Enthusiast

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
16 - Uranus
16 - Uranus

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
13 - Mars
13 - Mars

What do you mean with bad number? It works:

Captura de pantalla 2018-09-05_12-26-08_p. m..png

Aaron_Vreeland
5 - Automation Enthusiast
5 - Automation Enthusiast

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
14 - Jupiter
14 - Jupiter

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
13 - Mars
13 - Mars

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
13 - Mars
13 - Mars

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.