Search code examples
google-sheetsgoogle-sheets-formula

Find Average Using Two Conditions (with Different Data Types, number of days and currency)

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!

Link to Sheet Here

Solution

  • You're not specifying AverageIfs first parameter (AVERAGE_RANGE). This should be the range where the numeric values exist. This also happens to be a column for one of your criteria_range conditions, which I think is where you got tripped up. It looks like you tried to use your CountIfs syntax, but CountIfs doesn't require any calculations beyond true/false, so it doesn't need the initial parameter for numeric data.

    For days your formula should be:

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

    For Amount:

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

    Other Options

    AverageIfs is probably the most practical approach, but here are a couple other options that populate both of your desired outcomes. You could put either of these formulas in any cell and it would populate both a header and the two averages (so four cells total).

    ={"Average Days","Average Cost";
Average(filter('NYC P&R'!E:E,('NYC P&R'!E:E>0)*('NYC P&R'!N:N>0))),
Average(filter('NYC P&R'!N:N,('NYC P&R'!E:E>0)*('NYC P&R'!N:N>0)))}

    As the other answer mentioned, the query function could be used this way as well...

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

    Both formulas would return:

    enter image description here