Excel: AVERAGE IF

Question

Lets say I have an Excel sheet such that:

Column 1 contains salaries
Column 2 contains gender (M/F)

How can I calculate the average salary for females?

Answer 1

Answered question, solid formulas - BUT - beware of conditional averages based on numbers:

In a very similar situation I tried:

"=AVERAGE(IF(F696:F824<0;E696:E824))" (shift control enter) - In English this asked Excel to calculate an average on all numbers in column E if a result in column F was negative (a loss) - e.g. "calculate an average for all items where a loss occured". (no circular reference)

When asked to calculate an average for all items where a gain (x>0) occured, Excel got it right. However, when the conditional average was based on a loss - Excel produced a huge error (7.53 instead of 28.27).

I then opened exactly the same document in Open Office, where Calc got the (correct) answer 28,27 from the same Array formula.

Recalculating the whole thing in steps in Excel (first new column of only losses, new for only gains, new column for only E-values where a loss/gain occured, then a "clean" (unconditional) average calculation, produced the correct values.

Thus, it should be noted that Excel and Open Office produce different answers (and Excel 2007, Swedish language version, gets them wrong) in some cases of conditional averages.

(sorry for my long cautionary tale - but be a bit careful when the condition is a number would be my advice)

Answer 2

To do it without an array formula just use this:

=SUMIF(B:B,"F",A:A)/COUNTIF(B:B,"F")

Answer 3

Allthough the answer is already answered/accepted I can't resist to add my 2 cents:

Sums and averages normally are displayed at the bottom of a list. You can use the SUBTOTAL() function to calculate sum and average and specify to include or exclude "hidden" values, i.e. values suppressed by a filter. So the solution could be:

• create a formula =SUBTOTAL(101,A2:A6) for the average
• create a formula =SUBTOTAL(109,A2:A6) for the sum
• create an autofilter on the Gender column

Now, when you filter for "F", "M" or all, the correct sum and average will always be computed.

Hope that helps - Good luck

Answer 4

Salaries gender
2500 M 0 2500*0
2400 F 1 2400×1
2300 F 1 2300×1
sum =2 sum=4700 average=4700/2

Maybe it be complex.

Answer 5

Or you can add additional column with female salaries only.

The formula would be like: =IF(B1 = "Female";A1)

Then you simply calculate average of newly created column.

Answer 6

=AVERAGE(IF(B1:B10="F",A1:A10))

entered as an array function (ie using Shift-CTRL-Enter rather than just Enter)

Answer 7

You can put filter in top line of your sheet. Then Filter only Fs from Column2, then calculate average of values.