Averaging

  • Thread starter Thread starter Roger
  • Start date Start date
R

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.
 
Use this array formula:

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

just install it with a CNTRL-SHFT-ENTER rather than just an ENTER
 
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
 
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
 
Are you sure that it need array entry? It seems to work for me with normal
entry.
Click to expand...

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
 
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
 
Back
Top