Results 1 to 8 of 8

formula to work out annual averages based of the corresponding date in adjacent column??

Threaded View

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    Birmingham
    MS-Off Ver
    2016
    Posts
    27

    Question formula to work out annual averages based of the corresponding date in adjacent column??

    Hello,
    I have a spreadsheet with different people's scores for a test over time:
    A1:A1000 contains names
    B1:B1000 contains test 1 scores
    C1:C1000 contains test 1 dates
    D1:D1000 contains test 2 scores
    E1:E1000 contains test 2 dates etc. with space for up to 100 tests per person to be recorded.
    I am looking to create a formula that will calculate the annual average scores for each person for every year from 1995 to 2020, and these will be at the end of each row under the headings "1995 annual average" to "2020 annual average". This means the scores to be included for the calculation of each yearly average are dependent on their date. For example the 2017 calculation should only be an average of the scores with a corresponding date in the year 2017.
    I have tried labelling the data rage B:B,D:D,F:F,H:H...GS:GS "scores" and the data range C:C,E:E...GT:GT "dates" to put into the formula. I have tried a few variations including:
    AVERAGEIF(dates,AND(>=01/01/2017,<=31/12/2017),scores)
    AVERAGEIFS(scores,dates">="&01/01/2017,dates"<="&31/12/2017)
    IF(AND(dates(>=01/01/2017,<=31/12/2017),AVERAGE(scores)))
    These all come up with errors, I have also tried using =YEAR(2017) as the criteria and this won't work either. I am also not sure if I am being specific enough in these formulas to pick out the correct cells to use in each average based off the criteria of the date in the next cell - is this possible?
    Any suggestions would be much appreciated, Thanks

    EDIT: I Have added an example sheet showing the basic layout. We have multiple tests being carried out up to 100 times for >1000 people so the best way to measure their progression for each test seems to be to calculate their yearly average scores and then use those values to input into pivot tables?
    Attached Files Attached Files
    Last edited by lilybickel; 02-08-2021 at 05:27 AM. Reason: solved

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 05-30-2019, 06:18 AM
  2. Auto-adjust # of rows in a column that have a formula based on adjacent column data?
    By Norcal1 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 05-31-2018, 02:03 PM
  3. Replies: 3
    Last Post: 12-16-2015, 05:56 PM
  4. [SOLVED] Day of the week based on date in adjacent column
    By mknispel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-08-2014, 11:40 PM
  5. Sum based on date range by workdays in adjacent column
    By papasmurfuo9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2014, 10:31 AM
  6. Counting unique items on a list based on date in adjacent column
    By Mafoo17 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2013, 04:24 PM
  7. Replies: 1
    Last Post: 01-05-2011, 05:18 PM

Tags for this Thread

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