Standard Deviation & Weighted Average

[fcalvillo]

I need to calculate the standard deviation and weighted average of a large set of data. A sample of the data:

Job Title Frequency Rate
Proj Mgr 1 4 22.00
Proj Mgr 1 2 22.53
Proj Mgr 1 5 21.13
Proj Mgr 1 2 23.88
Proj Mgr 1 1 20.07
Proj Mgr 2 4 25.34
Proj Mgr 2 5 26.12
Proj Mgr 2 3 24.78
...

All of the data is within a single sheet, but there are literally thousands of rows (year's worth of data) and dozens of Job Titles. How can I produce a report similar to:

Job Title Low Rate High Rate Weighted Avg Std Dev
Proj Mgr 1 20.07 23.88 21.90 1.44
Proj Mgr 2 ...

I know I can use SUMPRODUCT/SUM to get Weighted Average and STDEV to get Standard Deviation for the individual pieces but that would take forever. Can this be done using a macro?

Thanks in advance!

 

[shg]

I think whatever you do with well-constructed formulas will be faster than VBA.

 

[xladept]

Hi fcalvillo,

In this instance I think you're better off to do the calculations yourself.
I'd load up a UDT Array indexed by Program Manager.

There are several concerns: f.r. are there any program managers with less than 30 records - and wouldn't you like to get the total standard deviation
The weighted average is the mean of the data - so after loading the UDT array - you could sort it with a bubble sort if the data isn't in order - etc. etc.

And the UDT Array would be persistent - you could use it all day!


<table border="0" cellpadding="0" cellspacing="0" width="342"><colgroup><col width="342"></colgroup><tbody><tr height="17"> <td style="height:12.75pt;width:257pt" height="17" width="342">You can't do one thing. XLAdept</td> </tr></tbody></table>