I was given a case describing projects -- their budgets, schedule, etc.

I've identified those that are over budget and over schedule ('Personal Findings' cell B14)

=COUNTIFS('NYC P&R'!I:I,">0",'NYC P&R'!N:N,">0")

Now I need to find the average of each using this condition -- Of those projects that are over budget and over schedule, what are the average number of days over schedule? What is the average over budget?

For the average number of days (with conditions), I've tried this input:

=AVERAGEIFS('NYC P&R'!E:E,">0",'NYC P&R'!N:N,">0)")

But I'm then met with: AVERAGE​IFS expect all arguments after position 3 to be in pairs.

I don't know if I'm inputting the data wrong, using the function wrong, or need an entirely different function.

Any help would be greatly appreciated. Thank you in advance!

CodePudding user response：

Consider learning the QUERY formula. It is powerful, and opens up plenty of possibilities. It uses the Google Visualization API Query Language.

For your specific requirement, the formula would look something like:

=QUERY('NYC P&R'!A:P, "SELECT AVG(N) WHERE E > 0 and N > 0 LABEL AVG(N) ''", -1)

It returns average of column N (total days overdue) for all entries that have a > 0 value in columns E (design schedule overdue) and N.

Of course, you could use different columns in the condition based on your requirement, and there's a fairly exhaustive list of comparison operators for all data types you'd typically work with.

The LABEL AVG(N) '' part at the end is to get rid of the unnecessary (depending on the use case) heading that comes back with the result.