conditional average in excel

问题:

I have an excel sheet where these are the columns:

% of contract value = c(4,3,5,6)

contract type = c("sell", "sell", "sell", "buy")

confidence = c(5, 3, 3, 3).

I would like to do a conditional average as follows:

take the average of % of contract value but only for rows where contract type = "sell" and confidence = 3.

In other words the answer to this should be (3 + 5)/2 = 4

Is there an easy way to do this in excel????

回答1:

What is the purpose of the c(bla,bla,bla) notation if this is in an Excel worksheet? If your data is arranged in a table, then you can easily use the AverageIfs function.

=AVERAGEIFS(B2:B5,C2:C5,"sell",D2:D5,3)