Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    30

    Unanswered: Average calculation

    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. #2
    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. #3
    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
  •