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

lazebra

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

MrKowz

Well-known Member
Welcome to the forums!

Try:

lazebra

New Member
Thank you so much MrKowz! aka MrKnow!

1,106,040
Messages
5,508,936
Members
408,702
Latest member
daz457

This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...