Conditional Average
 

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?

Conditional Average
 

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)

--
Biff
Microsoft Excel MVP


"Eki75" wrote in message
...
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?

Conditional Average
 

Try
=SUMPRODUCT(--(B5:B16="BLONDE"),C5:C16)/SUMPRODUCT(--(B5:B16="BLONDE"),--(C5:C16<""))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eki75" wrote in message
...
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?

Conditional Average
 

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"
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eki75" wrote in message
...
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?

Conditional Average
 

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!

"Bernard Liengme" wrote:

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"
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eki75" wrote in message
...
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?

Conditional Average
 

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



"Eki75" wrote:

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!

"Bernard Liengme" wrote:

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"
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eki75" wrote in message
...
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?

Conditional Average
 

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** :

=AVERAGE(IF((ISNUMBER(MATCH(B5:B16,E5:E6,0)))*(C5: C16<""),C5:C16))

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


--
Biff
Microsoft Excel MVP


"Eki75" wrote in message
...
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!

"Bernard Liengme" wrote:

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"
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eki75" wrote in message
...
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?

Conditional Average
 

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" wrote:

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** :

=AVERAGE(IF((ISNUMBER(MATCH(B5:B16,E5:E6,0)))*(C5: C16<""),C5:C16))

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


--
Biff
Microsoft Excel MVP


"Eki75" wrote in message
...
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!

"Bernard Liengme" wrote:

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"
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eki75" wrote in message
...
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?

Conditional Average
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Eki75" wrote in message
...
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" wrote:

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** :

=AVERAGE(IF((ISNUMBER(MATCH(B5:B16,E5:E6,0)))*(C5: C16<""),C5:C16))

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


--
Biff
Microsoft Excel MVP


"Eki75" wrote in message
...
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!

"Bernard Liengme" wrote:

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"
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eki75" wrote in message
...
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?