Conditional Average

E

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

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)
 
B

Bernard Liengme

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

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"
 
E

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!
 
D

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

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))
 
E

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top