Hi. Is there a formula to average multiple occurences of the same numbers without using a range?

[lazebra]

For example:

I have eighty three occurences of 5; ten occurences of 4; and two occurences of 3

In a range the average is 4.85

But I want to use a formula to show within this worksheet:
<TABLE style="WIDTH: 700pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=932 border=0><COLGROUP><COL style="WIDTH: 42pt" width=56><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3840" width=90><COL style="WIDTH: 42pt" span=2 width=56><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3584" width=84><COL style="WIDTH: 42pt" span=3 width=56><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2773" span=2 width=65><COL style="WIDTH: 42pt" width=56><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3541" width=83><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 1066" width=25><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2346" width=55><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 3114" width=73><TBODY><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5 0.5pt solid; WIDTH: 42pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; HEIGHT: 30.75pt; BACKGROUND-COLOR: transparent" width=56 height=41>Month</TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 68pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>Total Surveys Completed</TD><TD class=xl70 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 42pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>Excellent</TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 42pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>%of tot</TD><TD class=xl70 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 63pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>Above Avg</TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 42pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>%of tot</TD><TD class=xl70 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 42pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>Avg</TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 42pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>%of tot</TD><TD class=xl70 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 49pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=65>Below Avg</TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 49pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=65>%of tot</TD><TD class=xl70 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 42pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=56>Poor</TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 62pt; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" width=83>%of tot</TD><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #a5a5a5 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 19pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #948b54" width=25> </TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf 0.5pt solid; BORDER-LEFT: #bfbfbf; WIDTH: 41pt; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: transparent" width=55>Month</TD><TD class=xl73 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf 0.5pt solid; BORDER-LEFT: #bfbfbf; WIDTH: 55pt; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: yellow" width=73>Point Translation</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5 0.5pt solid; BORDER-BOTTOM: #a5a5a5 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>Jan</TD><TD class=xl65 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>95</TD><TD class=xl65 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>83</TD><TD class=xl66 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>87.37%</TD><TD class=xl65 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>10</TD><TD class=xl66 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>10.53%</TD><TD class=xl65 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD><TD class=xl66 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2.11%</TD><TD class=xl65 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl66 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0.00%</TD><TD class=xl65 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl66 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: #a5a5a5; BORDER-LEFT: #a5a5a5; BORDER-BOTTOM: #a5a5a5 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0.00%</TD><TD class=xl63 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #948b54"> </TD><TD class=xl67 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf 0.5pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: transparent">Jan</TD><TD class=xl68 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: yellow" align=right>4.85</TD></TR></TBODY></TABLE>

(Excellent = 5; Above Average = 4; Average = 3.....)

 

[MrKowz]

Welcome to the forums!

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">Month</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">Total Surveys Completed</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #EFEFEF;;">Excellent</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">%of tot</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #EFEFEF;;">Above Avg</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">%of tot</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #EFEFEF;;">Avg</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">%of tot</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #EFEFEF;;">Below Avg</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">%of tot</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #EFEFEF;;">Poor</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">%of tot</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #948B54;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">Month</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #FFFF00;;">Point Translation</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">Jan</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">95</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">83</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">87.37%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">10.53%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">2.11%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">0.00%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">0.00%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #948B54;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EFEFEF;;">Jan</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">4.85263158</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">=SUM(<font color="Blue">C2*5,E2*4,G2*3,I2*2,K2*1</font>)/SUM(<font color="Blue">C2,E2,G2,I2,K2</font>)</td></tr></tbody></table></td></tr></table><br />

 

[lazebra]

Thank you so much MrKowz! aka MrKnow!