Finding Weighted Average

[tahmed5]

Problem: We searched a total number of cases (total cases searched) and found errors in x number of cases (cases found). The average errors in those number of cases found is "average mistakes per case". These cases were searched using 3 different softwares: Non-CAC, CAC, and Legacy Scrubber. Now, we know the average mistake per case as a total average based on what software we used. However, we would like to find a weighted average as some facilities had more cases searched than others. What would be the best way to do this? Any help is appreciated!

[TABLE="width: 754"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 754"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Facility[/TD]
[TD]Cases Found[/TD]
[TD]Total Cases Searched[/TD]
[TD]Average Mistakes Per Case[/TD]
[TD]Software[/TD]
[/TR]
[TR]
[TD]Facility 3[/TD]
[TD]2049[/TD]
[TD]48875[/TD]
[TD]2.93549[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 4[/TD]
[TD]340[/TD]
[TD]4051[/TD]
[TD]1.79942[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 7[/TD]
[TD]5916[/TD]
[TD]70773[/TD]
[TD]1.64195[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 8[/TD]
[TD]246[/TD]
[TD]3046[/TD]
[TD]2.25269[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 9[/TD]
[TD]101[/TD]
[TD]1652[/TD]
[TD]2.24559[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 10[/TD]
[TD]2952[/TD]
[TD]23052[/TD]
[TD]2.05381[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 13[/TD]
[TD]465[/TD]
[TD]4667[/TD]
[TD]1.53263[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 14[/TD]
[TD]142[/TD]
[TD]3974[/TD]
[TD]1.88678[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 15[/TD]
[TD]286[/TD]
[TD]3002[/TD]
[TD]1.30938[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 16[/TD]
[TD]451[/TD]
[TD]6030[/TD]
[TD]1.08416[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 17[/TD]
[TD]304[/TD]
[TD]3709[/TD]
[TD]1.29583[/TD]
[TD]CAC[/TD]
[/TR]
[TR]
[TD]Facility 5[/TD]
[TD]9[/TD]
[TD]319[/TD]
[TD]5.59173[/TD]
[TD]Legacy Scrubber[/TD]
[/TR]
[TR]
[TD]Facility 11[/TD]
[TD]374[/TD]
[TD]6979[/TD]
[TD]1.60053[/TD]
[TD]Legacy Scrubber[/TD]
[/TR]
[TR]
[TD]Facility 1[/TD]
[TD]426[/TD]
[TD]3040[/TD]
[TD]0.81544[/TD]
[TD]Non-CAC[/TD]
[/TR]
[TR]
[TD]Facility 2[/TD]
[TD]308[/TD]
[TD]2406[/TD]
[TD]1.02992[/TD]
[TD]Non-CAC[/TD]
[/TR]
[TR]
[TD]Facility 6[/TD]
[TD]22[/TD]
[TD]100[/TD]
[TD]2.91202[/TD]
[TD]Non-CAC
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 511"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Software[/TD]
[TD]Average Mistake Per Case[/TD]
[TD]Weighted Average Mistake Per Case[/TD]
[/TR]
[TR]
[TD]CAC[/TD]
[TD]2.19074[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Legacy Scrubber[/TD]
[TD]1.70971[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Non-CAC[/TD]
[TD]1.43462[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[jarjarbingie]

Re: Help Finding Weighted Average

problem: We searched a total number of cases (total cases searched) and found errors in x number of cases (cases found). The average errors in those number of cases found is "average mistakes per case". These cases were searched using 3 different softwares: Non-cac, cac, and legacy scrubber. Now, we know the average mistake per case as a total average based on what software we used. However, we would like to find a weighted average as some facilities had more cases searched than others. What would be the best way to do this? Any help is appreciated!

[TABLE="width: 754"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 754"]
<tbody>[TR]
[TD]facility[/TD]
[TD]cases found[/TD]
[TD]total cases searched[/TD]
[TD]average mistakes per case[/TD]
[TD]software[/TD]
[/TR]
[TR]
[TD]facility 3[/TD]
[TD]2049[/TD]
[TD]48875[/TD]
[TD]2.93549[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 4[/TD]
[TD]340[/TD]
[TD]4051[/TD]
[TD]1.79942[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 7[/TD]
[TD]5916[/TD]
[TD]70773[/TD]
[TD]1.64195[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 8[/TD]
[TD]246[/TD]
[TD]3046[/TD]
[TD]2.25269[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 9[/TD]
[TD]101[/TD]
[TD]1652[/TD]
[TD]2.24559[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 10[/TD]
[TD]2952[/TD]
[TD]23052[/TD]
[TD]2.05381[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 13[/TD]
[TD]465[/TD]
[TD]4667[/TD]
[TD]1.53263[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 14[/TD]
[TD]142[/TD]
[TD]3974[/TD]
[TD]1.88678[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 15[/TD]
[TD]286[/TD]
[TD]3002[/TD]
[TD]1.30938[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 16[/TD]
[TD]451[/TD]
[TD]6030[/TD]
[TD]1.08416[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 17[/TD]
[TD]304[/TD]
[TD]3709[/TD]
[TD]1.29583[/TD]
[TD]cac[/TD]
[/TR]
[TR]
[TD]facility 5[/TD]
[TD]9[/TD]
[TD]319[/TD]
[TD]5.59173[/TD]
[TD]legacy scrubber[/TD]
[/TR]
[TR]
[TD]facility 11[/TD]
[TD]374[/TD]
[TD]6979[/TD]
[TD]1.60053[/TD]
[TD]legacy scrubber[/TD]
[/TR]
[TR]
[TD]facility 1[/TD]
[TD]426[/TD]
[TD]3040[/TD]
[TD]0.81544[/TD]
[TD]non-cac[/TD]
[/TR]
[TR]
[TD]facility 2[/TD]
[TD]308[/TD]
[TD]2406[/TD]
[TD]1.02992[/TD]
[TD]non-cac[/TD]
[/TR]
[TR]
[TD]facility 6[/TD]
[TD]22[/TD]
[TD]100[/TD]
[TD]2.91202[/TD]
[TD]non-cac[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 511"]
<tbody>[TR]
[TD]software[/TD]
[TD]average mistake per case[/TD]
[TD]weighted average mistake per case[/TD]
[/TR]
[TR]
[TD]cac[/TD]
[TD]2.19074[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]legacy scrubber[/TD]
[TD]1.70971[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]non-cac[/TD]
[TD]1.43462[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


thanks!

=SUMPRODUCT(--($E$2:$E$17=$J2),$D$2:$D$17,$C$2:$C$17)/SUMPRODUCT(--($E$2:$E$17=$J2),$C$2:$C$17)

where J2=CAC