Averageif Command - Does it Exist?

[Philip_Belle]

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”)

[Justin_Barrett]

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.

[Philip_Belle]

Justin,

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

Philip

[W_Vann_Hall]

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.

Airtable

No-Div-if-0 demo - Airtable

Explore the "No-Div-if-0 demo" base on Airtable.