Need to build average formula based on chaning criteria

dmingle

New Member
Joined
Oct 16, 2011
Messages
8
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 ---
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0

Forum statistics

Threads
1,224,392
Messages
6,178,316
Members
452,840
Latest member
Kbean

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top