Averageif and AND functions

Options

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

Tags:

Best Answer

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @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
    Options

    @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
    Leibel S ✭✭✭✭✭✭
    Options

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

  • Marlayna Sullivan
    edited 01/14/22
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!