Averaging

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger - 20 Oct 2007 15:30 GMT

Hi,

How do you average a column of numbers conditional on another column
being true?

w 4
w 5
w 6
l 100
w 9

In this case I want to average column b if column a =w getting an
answer of 6.

I tried =if($a$1:a5="w",average($b$1:b5)), but that doesn't get it
done.

Thanks for any explanation.

Reply to this Message

Gary''s Student - 20 Oct 2007 15:57 GMT

Use this array formula:

=AVERAGE(IF(A1:A5="w",B1:B5))

just install it with a CNTRL-SHFT-ENTER rather than just an ENTER

Signature

Gary''s Student - gsnu2007

> Hi,
>
[quoted text clipped - 14 lines]
>
> Thanks for any explanation.

Reply to this Message

Sandy Mann - 20 Oct 2007 16:09 GMT

Are you sure that it need array entry? It seems to work for me with normal
entry.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Use this array formula:
>
[quoted text clipped - 20 lines]
>>
>> Thanks for any explanation.

Reply to this Message

Sandy Mann - 20 Oct 2007 16:24 GMT

Silly me! Of course it does! I had array entered it and never even noticed!
Let call it a senior moment.

Signature

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Are you sure that it need array entry? It seems to work for me with
> normal entry.
[quoted text clipped - 23 lines]
>>>
>>> Thanks for any explanation.

Reply to this Message

Roger - 20 Oct 2007 18:54 GMT

>Are you sure that it need array entry? It seems to work for me with normal
>entry.

Thanks to all. I have a question about array's. Well, I don't really
get them. In the solution posted, why does it have to be an array?
What exactly does that accomplish?

Thanks

Reply to this Message

Sandy Mann - 20 Oct 2007 20:06 GMT

An IF() formula will normally have one test which returns one of two
options. In this formula we want the IF() part of the formula to loop
through 5 tests returning an array of returns of either the appropriate
value in Column B or the boolean value FALSE, ie in your example:

{4, 5, 6, FALSE, 9}

Which the AVERAGE() function, which ignores Booleans, evaluates to the
average that you want.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>>Are you sure that it need array entry? It seems to work for me with
>>normal
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message