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


Venky wrote:
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" wrote:


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

Venky wrote:

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

"Frank Kabel" wrote:



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

See:
http://www.dicks-blog.com/archives/2...las/trackback/

--
Regards
Frank Kabel
Frankfurt, Germany
"Ted Metro" schrieb im Newsbeitrag
...


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?