AVERAGE (But only IF)

[Brampton76]

I have 4 named Columns, for simplicity they are: 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.

 

[Pete_UK]

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

 

[T. Valko]

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)

 

[Brampton76]

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.

 

[T. Valko]

Maybe you should consider using dynamic ranges:

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

 

[Brampton76]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!