1. Registered User
Join Date
Mar 2010
Posts
30

Hello,

I'm trying to calculate an average and STD of a very long list of number. In column A I have a list of numbers from 1 to 17. In column B I have a number that tells me how many times the number in column A appears in my raw data.
For example- cell A2 has the number 1 and cell B2 has the number 340, which measn that in my raw data, the number 1 appears 340 times. Unfortunately, I cannot use the raw data to calculate the average. Here is the data I have:

A B
1 340
2 223
3 146
4 101
5 54
6 32
7 23
8 12
9 8
10 2
11 7
12 5
13 2
14 1
15 1
16 0
17 1

I manually calculated the average of 2.76 and the STD of 2.22, however, i'm unable to find a way to calcuate this without creating a long column of all those numbers.

Any help will be great!
Thanks a lot!

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

Assume your sample data is in cells A1:B17.

To get the average, put this formula in cell E1:
=SUMPRODUCT(A1:A17,B1:B17)/SUM(B1:B17)

To get the standard deviation, put this formula is cell E2:
=(SUMPRODUCT((A1:A17-E1)^2,B1:B17)/SUM(B1:B17))^(1/2)

3. Registered User
Join Date
Mar 2010
Posts
30
Thanks a lot Colin, it worked like a charm!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•