+ Reply to Thread
Results 1 to 5 of 5

Finding the average

  1. #1
    Registered User
    Join Date
    01-15-2022
    Location
    turkey
    MS-Off Ver
    2021
    Posts
    2

    Finding the average

    I have a list including some survey scores (about 10.000 rows) as follows:
    For example;

    1.jpg

    I want to achieve the following 4 things:

    -I want to find the average of the survey points in 2020
    -I want to find the average of the survey points in 2021
    -I want to find the average of the scores of 2020 and 2021.
    -The survey includes about 10.000 rows. So, how to automatize it? (It is very time consuming to manually find the teacher names in column A and find the averages for each of them)




    Maybe we can add them as new columns as follows:
    Attachment 763580

    Please find the excel file in the attachment.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by student_md; 01-15-2022 at 10:54 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,993

    Re: Finding the average

    J2, copied across and down:

    =IFERROR(AVERAGEIFS($G:$G,$A:$A,$I2,$C:$C,J$1),"")
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,993

    Re: Finding the average

    a few issues.

    1. the value in G10 is stored as TEXT, not a number. Delete & replace with 8.

    2. You wanted an average for 2020 and 2021 combined. There are TWO ways of approaching it. Your approach averaged the average. You need to be ABSOLUTELY certain that that is what you REALLY want to do!!! This ofrmula averages the ALL James' scores for BOTH years and produces a different answer.

    To take it to an extreme... if the average for 100 results in 2020 was 9 and the average for 2 results in 2021 was 1.... would you REALLY want the overall average to be 5, the average of the average??


    This produces the true average:

    =SUM(SUMIFS(G:G,A:A,I2,C:C,{2020,2021}))/SUM(COUNTIFS(A:A,I2,C:C,{2020,2021}))
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-15-2022
    Location
    turkey
    MS-Off Ver
    2021
    Posts
    2

    Re: Finding the average

    Dear Gleen,
    thank you for your interest.

    You are right. I didn't explain well what I wanted.

    I have edited my post.
    Could you help me?
    Thank you.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,993

    Re: Finding the average

    Will there be more than 2 years data? If so, will the average of years be JUST 2 years, or all of them. This is for each individual year:

    =IFERROR(SUMPRODUCT(($A$2:$A$10=$H2)*($C$2:$C$10=I$1)*$D$2:$D$10*$E$2:$E$10)/SUMIFS($E:$E,$A:$A,$H2,$C:$C,I$1),"")
    copied across 1 column & then down.

    This is for ALL years (average of 5 years, if 5 years data are present)

    =IFERROR(SUMPRODUCT(($A$2:$A$10=$H2)*$D$2:$D$10*$E$2:$E$10)/SUMIFS($E:$E,$A:$A,$H2),"")

    It will need a tweak if it needs to be two out of n years.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding the average of two dates.
    By sungen99 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-14-2015, 09:51 AM
  2. Finding an Average for times
    By hrshu in forum Excel General
    Replies: 4
    Last Post: 07-12-2015, 01:18 PM
  3. [SOLVED] Issue finding an average
    By The Man With No Name in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-08-2014, 02:47 PM
  4. Finding A missing # From the AVERAGE
    By onyxhacker in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2014, 05:56 PM
  5. Help finding Average in Data Set
    By andyaf in forum Excel General
    Replies: 2
    Last Post: 01-31-2014, 02:56 AM
  6. Finding Average
    By JezLisle in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-27-2009, 10:47 AM
  7. Finding an Average
    By dmay1102 in forum Excel General
    Replies: 11
    Last Post: 07-09-2008, 02:36 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1