AVERAGE (But only IF)
 

I have 4 named Columns, for simplicity they a Code, Red, Blue and Green.
In the Code Column are the numbers 1,2, or 3 and are random. The Red, Blue
and Green Columns contain numbers anywhere between 1 and 100. But, not all
the rows in the coloured columns contain numbers. I am trying to find the
Average of each of the coloured Columns but only where I specific a code. I
started with the following,
=SUMIF(Code,1,Red)/COUNTIF(Code,1)
but found that the sum was being divided by the total of the Code 1's and
not the number of cells that contain data. Unfortunately, I have tried all
sorts of other combinations but with my level of knowledge, have got no
further. I would be grateful for some guidance.
--
Glenn

AVERAGE (But only IF)
 

Try this array* formula:

=AVERAGE(IF(Code=1,Red))

* As this is an array formula, then once you have typed it in (or
subsequently amend it), you should use CTRL-SHIFT-ENTER to commit it
rather than the usual ENTER. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - you should not type these yourself.

The named ranges should all have the same number of cells.

Hope this helps.

Pete

On Feb 7, 6:30*pm, Brampton76
wrote:
I have 4 named Columns, for simplicity they a Code, Red, Blue and Green.. *
In the Code Column are the numbers 1,2, or 3 and are random. *The Red, Blue
and Green Columns contain numbers anywhere between 1 and 100. *But, not all
the rows in the coloured columns contain numbers. *I am trying to find the
Average of each of the coloured Columns but only where I specific a code. *I
started with the following,
=SUMIF(Code,1,Red)/COUNTIF(Code,1)
but found that the sum was being divided by the total of the Code 1's and
not the number of cells that contain data. *Unfortunately, I have tried all
sorts of other combinations but with my level of knowledge, have got no
further. *I would be grateful for some guidance.
--
Glenn

AVERAGE (But only IF)
 

Try this array formula** :

=AVERAGE(IF((Code=1)*(Red<""),Red))

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

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
I have 4 named Columns, for simplicity they a Code, Red, Blue and Green.
In the Code Column are the numbers 1,2, or 3 and are random. The Red,
Blue
and Green Columns contain numbers anywhere between 1 and 100. But, not
all
the rows in the coloured columns contain numbers. I am trying to find the
Average of each of the coloured Columns but only where I specific a code.
I
started with the following,
=SUMIF(Code,1,Red)/COUNTIF(Code,1)
but found that the sum was being divided by the total of the Code 1's and
not the number of cells that contain data. Unfortunately, I have tried
all
sorts of other combinations but with my level of knowledge, have got no
further. I would be grateful for some guidance.
--
Glenn

AVERAGE (But only IF)
 

Many thanks. I did find though, that my named columns had to be of the same
length as the data ie, I seem unable to name the whole column and just drop
the data in. I simply received a #NUM! error each time. That said, a
little bit of tweaking and I have still saved a fair bit of time using your
formula. Many thanks for everyones help.
--
Glenn


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((Code=1)*(Red<""),Red))

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

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
I have 4 named Columns, for simplicity they a Code, Red, Blue and Green.
In the Code Column are the numbers 1,2, or 3 and are random. The Red,
Blue
and Green Columns contain numbers anywhere between 1 and 100. But, not
all
the rows in the coloured columns contain numbers. I am trying to find the
Average of each of the coloured Columns but only where I specific a code.
I
started with the following,
=SUMIF(Code,1,Red)/COUNTIF(Code,1)
but found that the sum was being divided by the total of the Code 1's and
not the number of cells that contain data. Unfortunately, I have tried
all
sorts of other combinations but with my level of knowledge, have got no
further. I would be grateful for some guidance.
--
Glenn

AVERAGE (But only IF)
 

Maybe you should consider using dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
Many thanks. I did find though, that my named columns had to be of the
same
length as the data ie, I seem unable to name the whole column and just
drop
the data in. I simply received a #NUM! error each time. That said, a
little bit of tweaking and I have still saved a fair bit of time using
your
formula. Many thanks for everyones help.
--
Glenn


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((Code=1)*(Red<""),Red))

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

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
I have 4 named Columns, for simplicity they a Code, Red, Blue and
Green.
In the Code Column are the numbers 1,2, or 3 and are random. The Red,
Blue
and Green Columns contain numbers anywhere between 1 and 100. But, not
all
the rows in the coloured columns contain numbers. I am trying to find
the
Average of each of the coloured Columns but only where I specific a
code.
I
started with the following,
=SUMIF(Code,1,Red)/COUNTIF(Code,1)
but found that the sum was being divided by the total of the Code 1's
and
not the number of cells that contain data. Unfortunately, I have tried
all
sorts of other combinations but with my level of knowledge, have got no
further. I would be grateful for some guidance.
--
Glenn

AVERAGE (But only IF)
 

Again, many thanks, and also for the website link. Lots to learn!
--
Glenn


"T. Valko" wrote:

Maybe you should consider using dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
Many thanks. I did find though, that my named columns had to be of the
same
length as the data ie, I seem unable to name the whole column and just
drop
the data in. I simply received a #NUM! error each time. That said, a
little bit of tweaking and I have still saved a fair bit of time using
your
formula. Many thanks for everyones help.
--
Glenn


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((Code=1)*(Red<""),Red))

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

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
I have 4 named Columns, for simplicity they a Code, Red, Blue and
Green.
In the Code Column are the numbers 1,2, or 3 and are random. The Red,
Blue
and Green Columns contain numbers anywhere between 1 and 100. But, not
all
the rows in the coloured columns contain numbers. I am trying to find
the
Average of each of the coloured Columns but only where I specific a
code.
I
started with the following,
=SUMIF(Code,1,Red)/COUNTIF(Code,1)
but found that the sum was being divided by the total of the Code 1's
and
not the number of cells that contain data. Unfortunately, I have tried
all
sorts of other combinations but with my level of knowledge, have got no
further. I would be grateful for some guidance.
--
Glenn

AVERAGE (But only IF)
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
Again, many thanks, and also for the website link. Lots to learn!
--
Glenn


"T. Valko" wrote:

Maybe you should consider using dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
Many thanks. I did find though, that my named columns had to be of the
same
length as the data ie, I seem unable to name the whole column and just
drop
the data in. I simply received a #NUM! error each time. That said, a
little bit of tweaking and I have still saved a fair bit of time using
your
formula. Many thanks for everyones help.
--
Glenn


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((Code=1)*(Red<""),Red))

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

--
Biff
Microsoft Excel MVP


"Brampton76" wrote in message
...
I have 4 named Columns, for simplicity they a Code, Red, Blue and
Green.
In the Code Column are the numbers 1,2, or 3 and are random. The
Red,
Blue
and Green Columns contain numbers anywhere between 1 and 100. But,
not
all
the rows in the coloured columns contain numbers. I am trying to
find
the
Average of each of the coloured Columns but only where I specific a
code.
I
started with the following,
=SUMIF(Code,1,Red)/COUNTIF(Code,1)
but found that the sum was being divided by the total of the Code
1's
and
not the number of cells that contain data. Unfortunately, I have
tried
all
sorts of other combinations but with my level of knowledge, have got
no
further. I would be grateful for some guidance.
--
Glenn