average

[muizac]

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

 

[Andrew Poulsom]

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]

What Andrew is saying is that for frequency data


average = sum(fx) / sum(f)

 

[muizac]

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]

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

 

[muizac]

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

top work mate!

A