Calculating an Average based upon a value in other column

Kandee Evans · 04/08/19

I have a calculation to calculate the number of days between two columns.

=IFERROR(NETWORKDAYS([Date Submitted]41, [Go Live Date]41), " ") It works great to return a blank if the customer has not gone live.

Then I have formula to calculate the average of the cells.

=AVG([Number of Days to Implement]12:[Number of Days to Implement]41) Also works great !

I now want to calculate the average based upon a region (EAST, WEST, CENTRAL, MOUNTAIN)

I tried this formula but it is not working.

=AVG([Number of Days to Implement]12:[Number of Days to Implement]41, [US Region]:[US Region], "Central")

Any suggestions?

L_123 · 04/08/19

smartsheet does not currently have an avgif formula. I recommend submitting a product enhancement request in the bar to the right.

That said, you can achieve the same results by using a sumif()/Count()

=sumif([US Region]12:[US Region]41, "Central",Number of Days to Implement]12:[Number of Days to Implement]41)/Count([US Region]12:[US Region]41)

Paul Newcome · 04/08/19

You could also use an AVG(COLLECT( formula which works exactly how an AVGIFS would work.

=AVG(COLLECT([Number of Days to Implement]12:[Number of Days to Implement]41, [US Region]:[US Region], "Central"))

Kandee Evans · 04/09/19

Thank you to both for your assistance. The average days were not calculating correctly with the formula but I was able to make a slight adjustment based upon another calculation I have in my sheet.

I already had it calculating the total number of closed implementations.

=COUNTIFS([US Region]:[US Region], "East", Closed:Closed, "Yes")

So I used that cell to do the division and it worked perfectly.

=SUMIF([US Region]15:[US Region]44, "East", [Number of Days to Implement]15:[Number of Days to Implement]44) / COUNT([After Hours]2)

After Hours is the column name I have for the COUNTIF formula

Paul Newcome · 04/09/19

Glad you were able to find a working solution!

Kara Lumley · 07/02/19

Hi Kandee,

This may help in the future:

=AVERAGEIF(range, criteria, [average_range])

Returns the average of cells that meet a given criteria.

Example: =AVERAGEIF([Value 1]200:[Value 2]280, >100)

Best,

Kara

L_123 · 07/03/19

Kara, when was this added? i double checked the formula list and even checked it in a blank sheet to make sure that averageif wasn't a function yet when i posted. I also checked the release notes and there is no mention of it

Andrée Starå · 07/03/19

Hi,

It was added yesterday, or at least I noticed it yesterday.

There are a lot of new functions.

Heres more info about AVERAGEIF: https://help.smartsheet.com/function/averageif

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Calculating an Average based upon a value in other column

Comments

Help Article Resources

Categories