Averageif and AND functions

Marlayna Sullivan

Hello,

I am lost and could really use some help. I am attempting to build a formula in the Sheet Summary to capture the Average Throughput of changes that are set with a particular Change Priority, Quarter, and Year. The formula I have is:

=AVERAGEIF(Throughput:Throughput, AND([Change Priority]:[Change Priority] = "Critical", [Closed Quarter]:[Closed Quarter] = "Q4", [Closed Year]:[Closed Year] = "2021"))

The above formula is returning #Invalid Operation. I will also add, you may be able to rule out any blank fields causing the issue. The sheet is set so that if the change is still OPEN, no data populates in the fields I am referencing.

I am sure I am not fulling understanding how to use the AND function and that may be the issue. All guidance is welcomed!

Thank you ~Marlayna

Genevieve P.

Hi @Marlayna Sullivan

Every function has a different structure. If you're now using AVERAGEIF, then this is the structure:

=AVERAGEIF(column with criteria, "Criteria", column to average)

See: https://help.smartsheet.com/function/averageif

It looks like you just have the column you want to average at the beginning instead of at the end! Try swapping this around and let me know if you still get an error.

Cheers,

Genevieve

Leibel S

@Marlayna Sullivan

The AND function would only work in this case if you had multiple criteria for the Throughput column (e.g. more than 100 and less than 1000).

In this case you would need to use the COLLECT function and feed that into the AVG function:

=AVG(COLLECT([Throughput]:[Throughput], [Change Priority]:[Change Priority], @cell = "Critical", [Closed Quarter]:[Closed Quarter], @cell = "Q4", [Closed Year]:[Closed Year], @cell = "2021"))

Marlayna Sullivan

@Leibel Shuchat

I had a feeling I was not using the AND function correctly however, when I enter the formula you have above it is giving a different error:

#DIVIDE BY ZERO

Any thoughts on why that might be happening?

Leibel S

That means that it is not returning any rows that match the criteria you put in

Marlayna Sullivan

Morning @Leibel Shuchat

I am using the Sheet Summary which I think might be causing some of the issue so rather than build the information from the fields, I switched tactics to use the other summaries that are functioning as expected. Here is the new formula:

=AVG(Throughput:Throughput, [Q3 2021 Critical Closed Count]#)

However the average is coming out wrong. It is calculating 158.47345 but when the cells are highlighted the average throughput is 60.78.

I also switched the formula to check the SUM and it is also showing incorrectly as 429780.

Genevieve P.

Hi @Marlayna Sullivan

In this most recent formula you're just using AVG. This will only Average the values you specify, without using an IF statement to filter down your rows. This means it's averaging all of the values in your Throughput column and averaging that in combination with the value in [Q3 2021 Critical Closed Count]#

See: https://help.smartsheet.com/function/avg

The AVG(COLLECT solution above is the best way to average one column based on multiple criteria listed in other columns.

If it's not working for you, can we check to see if it's finding your criteria? Use a COUNTIFS to count how many rows it's finding:

=COUNTIFS([Change Priority]:[Change Priority], @cell = "Critical", [Closed Quarter]:[Closed Quarter], @cell = "Q4", [Closed Year]:[Closed Year], @cell = "2021")

If this is finding the correct number of rows (you can check by adding a filter to the sheet), then this should work:

=AVG(COLLECT(Throughput:Throughput, [Change Priority]:[Change Priority], @cell = "Critical", [Closed Quarter]:[Closed Quarter], @cell = "Q4", [Closed Year]:[Closed Year], @cell = "2021"))

If this isn't working for you, it would be helpful to see a screen capture of the underlying sheet with the data and the columns, but please block out sensitive data.

Thanks!

Genevieve

Marlayna Sullivan

Hi @Genevieve P.

Thank you for following up on this item. I ended up creating a 'helper' column to gather a lot of the information (Quarter, Year, Priority) into one location but the formula is still not working correctly in the Sheet Summary.

Here is the new formula:

=AVERAGEIF(Throughput:Throughput, [Throughput Helper]:[Throughput Helper], ="Q4 2021 Critical")

It should be showing 72.07 days but I am getting #Invalid Data Type.

Genevieve P.

Hi @Marlayna Sullivan

Every function has a different structure. If you're now using AVERAGEIF, then this is the structure:

=AVERAGEIF(column with criteria, "Criteria", column to average)

See: https://help.smartsheet.com/function/averageif

It looks like you just have the column you want to average at the beginning instead of at the end! Try swapping this around and let me know if you still get an error.

Cheers,

Genevieve