Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Averageif Command - Does it Exist?

Topic Labels: Formulas
1123 3
cancel
Showing results for 
Search instead for 
Did you mean: 

In my Data I have some of the columns have Zero’s that are not relevant so dividing by any column with a Zero in it would not tell the truth at the end. Does this exist yet or is there a workaround?

Excel has this and it works so seems doable in Airtable unless I missing something.
=AVERAGEIF(A1:C10, “>0”)

3 Replies 3

There is currently no equivalent, and I can’t think of an easy workaround for it. I’d try thinking of a more complex one, but it’s late, and my brain is turning to Jell-o, but I may give it a think later when the thinker is willing to think.

If you haven’t already done so, you might consider adding a request for this to the Product Suggestions category.

Justin,

Thanks for your initial feedback. I knew this one world take a wee bit longer that a few minutes to tackle.

Philip

Well, you won’t be processing an entire column with any Airtable function, as a column represents the value of a given field across multiple records; Airtable functions are limited to calculations involving values in a single record.

If you’re talking about determining whether or not to perform an intra-record calculation, based on whether or not a certain field has a zero value, then, yes, it can be done. For example, imagine a [Month] table, one field of which is {Income}; for some months, {Income} may either be blank or have a value of $0.

Each record in a second table, [Year], is linked to as many as 12 [Month] records. Total annual income is calculated by a rollup field in [Year] with a SUM() aggregation function.

To determine the percentage of annual income contributed by each month with an {Income} greater than $0, define a field in [Month] that rolls up {Year::Annual Income} using the aggregation formula

IF(
    Income,
    Income/values*100
    )

As you can see from the following demonstration base, the percentage calculation is performed only for those months where {Income} is neither BLANK() nor 0.