Average If Formula

LouSnz

Is there such a thing as an Average If Formula in smartsheet?  so to average only cells that meet a certain criteria?

Shaine Greenwood

Hello—

 

We don't have an Average IF formula in Smartsheet but I've got your vote down for this on our enhancement request list for further consideration.

 

With being said, you can use a SUMIF formula to sum the values that meet your conditions, then divide it by a COUNTIF formula of the values that meet your conditions.

 

=SUMIF(...)/COUNTIF(...)

 

More on formulas here: https://help.smartsheet.com/articles/775363-using-formulas

Ally

Hello,

Just checking in to see if this functionality has been created yet? It would be very useful for my purposes, as well.

Thanks!

Ally

Bryan Gambatese

I too would like to use an averageIF. 

ggross

It has been about a year and a half since this was first reported/requested.  Do we have an idea of when this is going to be added?  AverageIF is a pretty common formula for PMs and would be a great asset for dashboard reporting.  Is it possible to get this prioritized in a near term release?

J. Craig Williams

You can use =AVG(COLLECT(range to sum, criteria range, criterion)

The intent, I believe, of COLLECT, is to avoid have special use case functions when one tool can satisfy multiple ones.

Craig

Chris Matthews

If the AverageIF formula still being worked on?  This would be a great feature to have. 

J. Craig Williams

See my post below on AVG(COLLECT()). 

My experience is that COLLECT()'s flexibility allows functions like AVERAGEIF to be created. This is NOT a work-around, but a design choice and a good one. 

My belief is that you will never see AVERAGEIF() as a stand-alone function.

The only use case I can see is porting of existing Excel / OpenOffice spreadsheets into Smartsheet, and that is an import problem, not a function problem.

Craig

CommAmanda

Yep - this function would still be useful.

(The problem with the Collect function is that it does not automatically adjust when columns are added). 

RJHola

When taking an average of data, and trying to create dashboards, you need this function to create the dashboard views at a filtered level.

If not, pivot tables to filter would truly be easier to build dashboards.

Brett Robinson

Craig, 

Do have a way to use this formula across sheets when you're trying to get the average of a child rows based on certain criteria? I got an #UNSUPPORTED CROSS-SHEET FORMULA error I think because children function is not supported.

Right now, I just added a helper column and linked the cell in the other sheet to the help column cell. However, I'd like to avoid that if possible. 

Thanks, 

Brett

J. Craig Williams

Brett,

On the source sheet, you will need to have some way to determine the rows that are CHILDREN. 

That additional range and criterion to the COLLECT's list of range/criteria on the destination sheet.

It is one more step and may clutter up the source sheet, may be additional admin burden when multiple source sheets are involved, but since COLLECT does not accept the hierarchy functions, there isn't really a viable alternative.

Craig

 

Kara Lumley

Hello everyone,

AverageIf is in the works, along with some other new functions. Please note that the timeline we are targeting for release (July) is subject to change and is not guaranteed. 

Thank you,

Kara

Andrée Starå

Excellent news Kara!

Can't wait!

Have a fantastic weekend!

Best,

Andrée

Tim Möhle

Add my vote to add this formula into SS!!