averaging values according to (very) complex conditions
 

Please help! There must be a way to make excel do this:

I'm trying to compute a formula to average values only if they mee
certain criteria.
I am working on outputs generated by Superlab Pro. The data are from a
experiment where trials are randomised.

I need Excel to average all values from, say column A providing tha
they correspond to condition x in column B (so average all the value
which are in a row that contains the term x in column B) , AN
condition y in column C AND condition z in column D.

The key problem is that I can't know in advance which rows will contai
the term x, y or z, so I wonder if there is a way to specify "any o
the rows which contain a specific term" or something similar....

I would be extrememly grateful for any advice. This would make my lif
so much easier.

Thanks,
Lior

--
Message posted from http://www.ExcelForum.com

averaging values according to (very) complex conditions
 

=SUMPRODUCT((A1:A100)*(B1:B100="myValue"))/COUNTIF(B1:B100,"myValue")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"liory " wrote in message
...
Please help! There must be a way to make excel do this:

I'm trying to compute a formula to average values only if they meet
certain criteria.
I am working on outputs generated by Superlab Pro. The data are from an
experiment where trials are randomised.

I need Excel to average all values from, say column A providing that
they correspond to condition x in column B (so average all the values
which are in a row that contains the term x in column B) , AND
condition y in column C AND condition z in column D.

The key problem is that I can't know in advance which rows will contain
the term x, y or z, so I wonder if there is a way to specify "any of
the rows which contain a specific term" or something similar....

I would be extrememly grateful for any advice. This would make my life
so much easier.

Thanks,
Liory


---
Message posted from http://www.ExcelForum.com/

averaging values according to (very) complex conditions
 

For only one condition, Faster would be:

=Sumif(B1:B100,"myValue",A1:A100)/COUNTIF(B1:B100,"myValue")

--
Regards,
Tom Ogilvy

"liory " wrote in message
...
Please help! There must be a way to make excel do this:

I'm trying to compute a formula to average values only if they meet
certain criteria.
I am working on outputs generated by Superlab Pro. The data are from an
experiment where trials are randomised.

I need Excel to average all values from, say column A providing that
they correspond to condition x in column B (so average all the values
which are in a row that contains the term x in column B) , AND
condition y in column C AND condition z in column D.

The key problem is that I can't know in advance which rows will contain
the term x, y or z, so I wonder if there is a way to specify "any of
the rows which contain a specific term" or something similar....

I would be extrememly grateful for any advice. This would make my life
so much easier.

Thanks,
Liory


---
Message posted from http://www.ExcelForum.com/

averaging values according to (very) complex conditions
 

Try the array formula:

=SUMPRODUCT(A1:A10,((B1:B10="x")*(C1:C10="y")*(D1: D10="z")))/SUM((B1:B10="x")*(C1:C10="y")*(D1:D10="z"))

enter with control+shift+enter to add braces around the whole.


liory < wrote:
Please help! There must be a way to make excel do this:

I'm trying to compute a formula to average values only if they meet
certain criteria.
I am working on outputs generated by Superlab Pro. The data are from an
experiment where trials are randomised.

I need Excel to average all values from, say column A providing that
they correspond to condition x in column B (so average all the values
which are in a row that contains the term x in column B) , AND
condition y in column C AND condition z in column D.

The key problem is that I can't know in advance which rows will contain
the term x, y or z, so I wonder if there is a way to specify "any of
the rows which contain a specific term" or something similar....

I would be extrememly grateful for any advice. This would make my life
so much easier.

Thanks,
Liory


---
Message posted from http://www.ExcelForum.com/

averaging values according to (very) complex conditions
 

JWolf's formula works.
Many many thanks!

--
Message posted from http://www.ExcelForum.com