Need help with averages based on criteria.

**mwmcginnis** · 02-27-2009, 06:06 AM

Hello all,

I'm a delivery driver and I've decided to start recording information about my deliveries: tip amount, age/race/se-x of the customer, etc.

So my problem is this: How can I display an average of tips based on the criteria of a different column?

For example one column records race as "W" for white, "B" for black, and "O" for other. Is there a way I can form an if statement to show me the average of only those values in column A(tips) where the corresponding value in column B(race)="W"?

**DonkeyOte** · 02-27-2009, 06:11 AM

Given you're using Excel 2007 .. you can use AVERAGEIF
(or AVERAGEIFS (multiple conditions))

=AVERAGEIF(B1:B100,"W",A1:A100)

would average A1:A100 where B1:B100 = "W"

see XL Help for more info.

**Steve R** · 02-27-2009, 06:14 AM

Column A tip, Column B Black/White/Other
=AVERAGE(IF(B1:B5="B",A1:A5,"")) + Ctrl + Shift + Enter

**DonkeyOte** · 02-27-2009, 06:18 AM

Steve R, you would need to enter that as an array w/CTRL + SHIFT + ENTER.

Given 2007 has built in non-array function it would make most sense to use it!

Also you don't need to use Null for FALSE ... a Logical FALSE will generate by default and obviously Logicals will be excluded in the average calculation, use:

=AVERAGE(IF(B1:B5="B",A1:A5))

Use the Evaluate formula tool for more info.