average on 2 conditions

[Guest]

suppose cells are a1:b4 have following values

east 5
west 7
north 6
east 3

so you can do an average like =average(if(a1:a4="east",b1:b4)) and that will
equal a value of 4 -- (5+3)/2

What if you wanted to calculate the average of numbers in a1:a4 where it
equaled 2 conditions - either east or west. So that would be 5 -- (5+3+7)/3.

How would the second formula be written to average numbers where either of
two conditions are met?

 

[Frank Kabel]

Hi
use
=average(if((a1:a4="east")+(A1:A4="west"),b1:b4))

See:
http://www.dicks-blog.com/archives/2004/12/04/logical-operations-in-array-formulas/trackback/

 

[Guest]

Does this formula work in specific versions of Excel. I tried reproducing
this case in Excel 2002. It didnt work. Using
=average(if(a1:a4="east",b1:b4)) gives me the result as 5.25 which is the
average of all the values put together and not 4.

Thanks

Venky

 

[Aladin Akyurek]

You need to confirm such formulas with control+shift+enter instead of
just with enter.

 

[Guest]

Thanks Aladin, That did work. Could you tell me why do we need to valdiate
using
control+shift+enter and not enter. Just curious to know

 

[Aladin Akyurek]

Control+shift+enter signals Excel that the formula in question should be
treated as an array-formula. The Help file contains material pertaining
to such formulas. Moreover, you might want to consult some of the
write-ups regarding such formulas:

www.emailoffice.com/excel/arrays-bobumlas.html
www.cpearson.com/excel/array.htm

 

[Guest]

Dear Aladin

Thanks So much for the information. This was more than helpful