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?

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

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?

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:b4))

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?

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:b4) )

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?

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:b4) )

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?

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?

Dear Aladin

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




"Aladin Akyurek" wrote:

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?