conditional statistics

[PeSt]

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

 

[ambracat]

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

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.

 

[Jim Cone]

Lapeste,
Here is an Average.If formula for you (data in B6:C12) ...
="Average: " &SUMIF(B6:B12,"=A",C6:C12)/COUNTIF(B6:B12,"=A")
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"PeSt" <[email protected]>
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

 

[Bernard Liengme]

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