Trying to use AverageIF.

Frederik Soerensen

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?

Thank you in advance

Bassam Khalil

@Frederik Soerensen

Please try the following formula:

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

the following screenshot shows the result

the result

is 50

Bassam Khalil

Hi @Frederik Soerensen

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.

Frederik Soerensen

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.

Frederik Soerensen

Here are screenshots of the columns

____

____

Bassam Khalil

@Frederik Soerensen

Please try the following formula:

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

the following screenshot shows the result

the result

is 50

Frederik Soerensen

Yes, this works perfectly, thank you.

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

Bassam Khalil

@Frederik Soerensen 

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

Bassam Khalil

@Frederik Soerensen 

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