Averaging

[Roger]

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.

 

[Guest]

Use this array formula:

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

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

 

[Sandy Mann]

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Roger]

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

 

[Sandy Mann]

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.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk