Conditional Average

[Eki75]

I'm trying to figure out a conditional average formula that ignores empty
cells.

Column a is demographic information (e.g., BLONDE,BRUNETTE,RED,GRAY,BLACK)
Column b is a number

I want to get an average of the numbers in Column B for which Column
A=BLONDE--BUT I want empty cells to be ignored.

I'm using: =SUMIF(B5:B16,"BLONDE",C5:C16)/COUNTIF(B5:B16,"BLONDE")

This works great except that it is counting empty cells. Any suggestions?

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF((B5:B16="blonde")*(C5:C16<>""),C5:C16))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Bernard Liengme]

Try
=SUMPRODUCT(--(B5:B16="BLONDE"),C5:C16)/SUMPRODUCT(--(B5:B16="BLONDE"),--(C5:C16<>""))
best wishes

 

[Bernard Liengme]

There you go: 2 very different solutions.
One need CTRL+SHIFT+ENTER, the other does not but is longer
My old Irish math teacher must have foreseen the coming of Excel: he
favourite saying was "there are more ways of killing a pig than stuffing it
with butter"

 

[Eki75]

Thank you guys. That worked for what I asked.

Is there a way to create multiple criteria for the average?
For example, column a is hair color, and column b is a number. Could I find
the average of only those with Blonde OR Brunette hair?

I have column Coded in such a way I though I could use a wildcard (*) to
make this work, but it returns either a #Value! error or a Div0 error.

I tried:
=SUMPRODUCT(--(B5:B16="BLONDE*"),C5:C16)/SUMPRODUCT(--(B5:B16="BLONDE*"),--(C5:C16<>""))
thinking it would catch Blonde AND Blondebrown AND blondish (for example),
but it doesn't work.

Thanks again for the speedy response!

 

[daddylonglegs]

Try

=SUMIF(B5:B16,"Blond*",C5:C16)/SUMPRODUCT(--(LEFT(B5:B16,5)="Blond"),--(C5:C16<>""))

or if you want to match say, blonde and brunette specifically

=SUMPRODUCT(--ISNUMBER(MATCH(B5:B16,{"Blonde","Brunette"},0)),C5:C16)/SUMPRODUCT(--ISNUMBER(MATCH(B5:B16,{"Blonde","Brunette"},0)),--(C5:C16<>""))

You can replace the {"Blonde","Brunette"} part with any number of categories
or even a cell range that lists those categories

 

[T. Valko]

Is there a way to create multiple criteria for the average?

average of only those with Blonde OR Brunette hair?

Use cells to hold the criteria:

E5 = Blonde
E6 = Brunette

Array entered** :

thinking it would catch Blonde AND Blondebrown AND blondish

SUMPRODUCT won't work with wildcards directly.

It's hard to get Excel to do fuzzy matching. There's a possibility of "false
positives" and missed matches.

You have to look for some common characteristic. For the above, "blond" is
that characteristic.

Array entered** :

=AVERAGE(IF((ISNUMBER(SEARCH("blond",B5:B16)))*(C5:C16<>""),C5:C16))

 

[Eki75]

Thanks guys. This works I think. I've spent the past two days trying to
figure it out on my own, so I appreciate the help!

 

[T. Valko]

You're welcome. Thanks for the feedback!