Seeking solution for averageifs()

Camila Parker

I would like to calculate the estimated hours it will take me to complete a task based on how long it took me to complete previous tasks of a similar nature. I've included a screenshot of my project sheet with the relevant columns.

Originally I was using the formula: AVERAGEIF([Deck Intensity]:[Deck Intensity], [Deck Intensity]@row, [Act. Hours]:[Act. Hours])

The problem is this formula doesn't take into account the [Status] value when deciding which rows/tasks to include in the average. I do not want to include rows/tasks that are "in progress" or "not started" in my calculation because it will skew my estimated hours lower than what it actually should be. It should only include the average for rows where [Status] is marked as "complete"

I've tried using several variations of =sumif()/countif() but they all end in an error code.

Here's my logic written out in case that helps anyone else:

In cell [Est. Hours]1, average the values in the [Act. Hours] column for every row where [Status] is equal to "Complete" AND [Deck Intensity] is equal to the value in [Deck Intensity]1.

Any ideas? Thanks in advance!

ker9

Hi @Camila Parker

See if this works for you:

=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, ="Complete", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))

Hope this helps!

ker9

Hi @Camila Parker

See if this works for you:

=AVG(COLLECT([Act. Hours]:[Act. Hours], Status:Status, ="Complete", [Deck Intensity]:[Deck Intensity], =[Deck Intensity]@row))

Hope this helps!

Camila Parker

@ker9 That worked! Thank you so much!