# average

#### muizac

##### Board Regular
Hi,

I think I'm being a bit mentally challenged with this one (first day back after the long weekend); I'm stuck. I have the number of channels people have and the # of instances they occured. I'm tring to find the average number of channels that people have. Is anyone able to help my tired brain?

Channel, count,
# 1 Channels, 1,
# 2 Channels, 1,
# 3 Channels, 9,
# 4 Channels, 17,
# 5 Channels, 31,
# 6 Channels, 13,
# 7 Channels, 2,
# 8 Channels, 1,
# > 8 Channels, 3,

Thank you

Andrew

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Andrew Poulsom

##### MrExcel MVP
I don't know how you would deal with > 8, but if the values 1 to 8 are in A2:A9 and the count is in B2:B9:

=SUMPRODUCT(A2:A9,B2:B9)/SUM(B2:B9)

will return the weighted average number of channels.

#### HarryS

##### Board Regular
What Andrew is saying is that for frequency data

average = sum(fx) / sum(f)

#### muizac

##### Board Regular
Hi,

Yes, a weighted average sounds about right - I'm looking at the data, around 4.9 channels per customer feels about right answer.

I tried the formula and it returned 0?

A2:A9 is the event (how many channels a customer could have)
B2:B9 is the instances of that event (ie the number of people who have purchased that amount of channels)

ie) cells A4 and B4 - show we have 9 customers in total who have purchased 3 channels.

Thanks,

Andrew

#### Andrew Poulsom

##### MrExcel MVP
You need the numbers 1 to 8 in A2:A9, not entries like # 1 Channels.

#### muizac

##### Board Regular
works like a charm Andrew, thanks heaps for your help.

top work mate!

A

Replies
1
Views
607
Replies
1
Views
234
Replies
2
Views
563
Replies
1
Views
480
Replies
1
Views
7K

1,191,173
Messages
5,985,095
Members
439,940
Latest member

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