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!

<tbody>
</tbody>

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Software	Average Mistake Per Case	Weighted Average Mistake Per Case
CAC	2.19074	?
Legacy Scrubber	1.70971	?
Non-CAC	1.43462	?

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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!

<tbody>
</tbody>

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

<tbody>
</tbody>

software	average mistake per case	weighted average mistake per case
cac	2.19074	?
legacy scrubber	1.70971	?
non-cac	1.43462	?

<tbody>
</tbody>

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