Need to build average formula based on chaning criteria

[dmingle]

Objective: to create an average variance among exact match RevCode

Example: all RevCodes with "666" should be used to make an average variance statistic

Situation: All RevCode's are not in similar group counts and this creates incorrect averages with a standard formula.

Set of Data
​

<table border="0" cellpadding="0" cellspacing="0" height="168" width="407"><colgroup><col style="width:48pt" span="3" width="64"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3299;width:70pt" width="93"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt;width:48pt" height="19" width="64">RevCode</td> <td class="xl68" style="width:48pt" width="64">Observed</td> <td class="xl68" style="width:48pt" width="64">Expected</td> <td class="xl69" style="width:48pt" width="64">Variance</td> <td class="xl70" style="width:70pt" width="93">RevCodeMean</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt" height="19">666</td> <td class="xl66">200</td> <td class="xl66">220</td> <td class="xl67">-9.091%</td> <td class="xl71">
</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt" height="19">666</td> <td class="xl66">205</td> <td class="xl66">220</td> <td class="xl67">-6.818%</td> <td class="xl67">
</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt" height="19">666</td> <td class="xl66">207</td> <td class="xl66">220</td> <td class="xl67">-5.909%</td> <td class="xl67" align="center">
</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt" height="19">777</td> <td class="xl66">500</td> <td class="xl66">355</td> <td class="xl67">40.845%</td> <td class="xl67">
</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt" height="19">767</td> <td class="xl66">505</td> <td class="xl66">505</td> <td class="xl67">0.000%</td> <td class="xl67">
</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt" height="19">668</td> <td class="xl66">100</td> <td class="xl66">105</td> <td class="xl67">-4.762%</td> <td class="xl67">
</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl66" style="height:14.4pt" height="19">668</td> <td class="xl66">105</td> <td class="xl66">105</td> <td class="xl67">0.000%</td> <td class="xl67">
</td> </tr> </tbody></table>

Formula that generates average variance between exact match revenue codes:
=(SUM(B2:B4)-SUM(C2:C4))/SUM(C2:C4)

The solutions can be in VBA or within the spreadsheet either way.

Thank you in advance for any help you can give me.


-- removed inline image ---

 

[dmingle]

I realized that I could use the following functions:

• Sumifs
• Averageifs
• Countifs

These functions only exist in 2007 and later.

Thought it was important to share in case someone else actually had the same questions.