# Trying to use AverageIF.

Options

Hello

I am trying to make a formula that counts the average amount of time an active (open) issue takes, whilst segregating it by priority (low, medium, high).

To count the total amount I used this formula: =SUMIFS({Time to fix}; {Solved}; 0; {Priority}; "LOW")

However I want to make a new formula that counts the "time to fix" if the "solved" column is not checked, and if the "priority" is set to LOW.

I tried with this formula: =AVERAGEIF({Time to fix}; IF({Solved}; 0); IF({Priority}; "LOW"))

This however, doesn't work. Is there a way to make it work?, or is there a smarter way to accomplish my task?

Tags:

• ✭✭✭✭✭✭
Options

=IFERROR(AVG(COLLECT({Time to fix}, {Solved}, 0, {Priority}, "Low")), "")

the following screenshot shows the result

the result

is 50

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

bassam.khalil2009@gmail.com

• Options

Here is the matrix where I want to use the formula

this is the formula used currently (of course with the priority changing accordingly):

=SUMIFS({Time to fix}; {Solved}; 0; {Priority}; "LOW")

In stead of counting the SUM, if want to count the average. That means; the average amount of time a low, medium and high priority issue takes, if the issued hasn't been closed.

the sheet that is referenced in the formula contains (among others) a "time to fix" column, a checkbox column, called "Solved", that is unmarked if the issue has not been closed, and a dropdown list column with the options; LOW, MEDIUM, HIGH.

• Options

Here are screenshots of the columns

____

____

• ✭✭✭✭✭✭
Options

=IFERROR(AVG(COLLECT({Time to fix}, {Solved}, 0, {Priority}, "Low")), "")

the following screenshot shows the result

the result

is 50

bassam.khalil2009@gmail.com

• Options

Yes, this works perfectly, thank you.

Can I ask; why is IFERROR the correct formula to use?

• ✭✭✭✭✭✭
Options

we usually use IFERROR to avoide any unexpected entry or Divide on zero or blank cells .. ATC

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

You are welcome and I will be happy to help you any time.

bassam.khalil2009@gmail.com