# Need to build average formula based on chaning criteria

#### dmingle

##### New Member
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.

-- removed inline image ---

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

Replies
1
Views
1K
Replies
1
Views
410
Replies
10
Views
927
Replies
0
Views
415
Replies
2
Views
490

1,203,241
Messages
6,054,330
Members
444,717
Latest member
melindanegron

### 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.

### Which adblocker are you using?

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

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