Closed Thread
Results 1 to 2 of 2

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

  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? Thanks

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,205

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

    Administrative Note:

    Welcome to the forum.

    Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

    Please see Forum Rule #5 about thread duplication.

    I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...nt-column.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] formula to work out annual averages based of the corresponding date in adjacent column??
    By lilybickel in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-04-2021, 06:15 AM
  2. Replies: 5
    Last Post: 05-30-2019, 06:18 AM
  3. 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
  4. Replies: 3
    Last Post: 12-16-2015, 05:56 PM
  5. [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
  6. 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
  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