Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average Formula (XL 2000)

    Hello, I am trying to figure out how I can do this (hoping I can explained it right). I enclosed a spreadsheet if you'd like to take a look. What I would like to do is to find out what the average is of a survey sheet. I have ratings 0 to 5, 5 being excellent 1 being poor.

    Once I have collected and entered the data from many different surveys on one sheet I come up with 5 people marking 1, 0 people 2, 2 people 3 and so on getting a total of 22 people rating the first "question" "How were the brochures/Handouts". Now I want to find out what is the average rating of that row? I created a formula =average(c12:H12) giving me a total of 3.67 if I have a zero in the empty cells, however if I delete the zeros I get 5.50 as a total????????? That doesn't sound right or is it??? Can someone help me on that? Thank you very much. Tira

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average Formula (XL 2000)

    What you want is a weighted average. The formula in J12 should be

    =SUMPRODUCT($D$11:$H$11,D12:H12)/SUM(D12:H12)

    You can fill this down to J15. Explanation: in the first place, you shouldn't include column C (rating 0 = don't know/doesn't apply) in the average. If 5 people give a rating of 1, 0 a rating of 2, 2 a rating of 3, 7 a rating of 4 and 8 a rating of 5, the total rating is 5*1 + 0*2 + 2*3 + 7*4 + 8*5. The number of people is 5 + 0 + 2 + 7 + 8. So the average rating is (5*1 + 0*2 + 2*3 + 7*4 + 8*5)/(5 + 0 + 2 + 7 + 8). This is precisely what the formula does.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Oakland, CA, USA
    Posts
    161
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average Formula (XL 2000)

    Wow, I guess I was a "little" off. I will try that on my actual sheet and hope it works. Thanks so much. Tira

Posting Permissions

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