AVERAGE (But only IF)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brampton76 - 07 Feb 2008 18:30 GMT

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.

Signature

Glenn

Reply to this Message

Pete_UK - 07 Feb 2008 19:04 GMT

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 <Brampto...@discussions.microsoft.com>
wrote:

> 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
[quoted text clipped - 9 lines]
> --
> Glenn

Reply to this Message

T. Valko - 07 Feb 2008 19:06 GMT

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)

Signature

Biff
Microsoft Excel MVP

>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,
[quoted text clipped - 11 lines]
> sorts of other combinations but with my level of knowledge, have got no
> further. I would be grateful for some guidance.

Reply to this Message

Brampton76 - 08 Feb 2008 06:55 GMT

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.

Signature

Glenn

> Try this array formula** :
>
[quoted text clipped - 18 lines]
> > sorts of other combinations but with my level of knowledge, have got no
> > further. I would be grateful for some guidance.

Reply to this Message

T. Valko - 08 Feb 2008 07:41 GMT

Maybe you should consider using dynamic ranges:

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

Signature

Biff
Microsoft Excel MVP

> Many thanks. I did find though, that my named columns had to be of the
> same
[quoted text clipped - 31 lines]
>> > sorts of other combinations but with my level of knowledge, have got no
>> > further. I would be grateful for some guidance.

Reply to this Message

Brampton76 - 08 Feb 2008 18:35 GMT

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

Signature

Glenn

> Maybe you should consider using dynamic ranges:
>
[quoted text clipped - 35 lines]
> >> > sorts of other combinations but with my level of knowledge, have got no
> >> > further. I would be grateful for some guidance.

Reply to this Message

T. Valko - 08 Feb 2008 19:48 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Again, many thanks, and also for the website link. Lots to learn!
>
[quoted text clipped - 43 lines]
>> >> > no
>> >> > further. I would be grateful for some guidance.

Reply to this Message