conditional statistics

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PeSt - 04 May 2008 15:55 GMT

Hello,

I have a range of conditions in a colums and a range of corresponding
measured values in other
I would like to make automatically basic statistics on the measured values.

OPERATOR MEASUREMENT
A 10
B 11
A 9
A 8
B 10
A 9
B 12

Now SUM.IF and COUNT.IF exist , but apparently AVERAGE.IF and STDDEV.IF or
MAX.IF and MIN.IF do not exist
Is there a macro of a function that could do this job ?

regards,

Lapeste

Reply to this Message

ambracat@aol.com - 04 May 2008 16:22 GMT

> Hello,
>
[quoted text clipped - 18 lines]
>
> Lapeste

I have been able to do a similar summarization using array formulas.
If have a limited number of possible entries for the "Operator"
column, you could define each column with the range names, then set up
a formulas such as the following:
{if(Operator="A",average(Measurement))}. Any function will work in
place of the "average" function. If you have an undetermined, or very
large number of possible entries for the "operator" column, that would
probably not be the best way.

Reply to this Message

Jim Cone - 04 May 2008 18:19 GMT

Lapeste,
Here is an Average.If formula for you (data in B6:C12) ...
="Average: " &SUMIF(B6:B12,"=A",C6:C12)/COUNTIF(B6:B12,"=A")

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"PeSt" <lapeste@scarlet.be>
wrote in message
Hello,
I have a range of conditions in a colums and a range of corresponding
measured values in other
I would like to make automatically basic statistics on the measured values.
OPERATOR MEASUREMENT
A 10
B 11
A 9
A 8
B 10
A 9
B 12

Now SUM.IF and COUNT.IF exist , but apparently AVERAGE.IF and STDDEV.IF or
MAX.IF and MIN.IF do not exist
Is there a macro of a function that could do this job ?
regards,
Lapeste

Reply to this Message

Bernard Liengme - 04 May 2008 21:06 GMT

Have you looked into Pivot Tables?
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Hello,
>
[quoted text clipped - 19 lines]
>
> Lapeste

Reply to this Message