+ Reply to Thread
Results 1 to 9 of 9

Get average of all averages that are in the same year

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Get average of all averages that are in the same year

    Hello.
    I'm not sure how to go about the following, I currently have an array that looks like this:
    Screenshot_1.png
    I would like to have a new array that shows the average of all 3 terms, for both Literacy and Fluency, for each year.
    So the new array should look like this:
    Screenshot_2.png

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Get average of all averages that are in the same year

    So I put your first table in A1:D7 and your second table in F1:H3 then I used this formula in G2:

    Please Login or Register  to view this content.
    I entered it as an array formula using Ctrl+Shift+Enter then copied it to G2:H3.

    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Get average of all averages that are in the same year

    Quote Originally Posted by WideBoyDixon View Post
    So I put your first table in A1:D7 and your second table in F1:H3 then I used this formula in G2:
    [...]
    I entered it as an array formula using Ctrl+Shift+Enter then copied it to G2:H3.
    WBD
    Thank you.
    I posted this question in the vba category as I would like to store the new array into a variable. My apologies for not specifying.

  4. #4
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Get average of all averages that are in the same year

    This is what I came up with so far but it breaks halfway:
    Please Login or Register  to view this content.
    Last edited by kosherboy; 10-14-2021 at 04:48 PM.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Get average of all averages that are in the same year

    An alternative is to use Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Get average of all averages that are in the same year

    Probably a bit overkill but:

    Please Login or Register  to view this content.
    WBD

  7. #7
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Get average of all averages that are in the same year

    Quote Originally Posted by WideBoyDixon View Post
    Probably a bit overkill but:
    [...]
    WBD
    Wow, thank you. Works like a charm.
    I appreciate the time you took to write it.

  8. #8
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Get average of all averages that are in the same year

    Quote Originally Posted by alansidman View Post
    An alternative is to use Power Query
    [...]
    I never heard of this, looks like this can be very useful. Thanks for sharing.

  9. #9
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Get average of all averages that are in the same year

    @kosherboy;

    The attached file shows an alternative using Pivot Table...

    .
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Haluk; 10-15-2021 at 02:34 AM.

+ 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. Hi team, i have a raw data file to find the averages of the each Year in a row.
    By viswanathchenna04 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2020, 09:26 AM
  2. Rolling averages and sums for the year
    By datacenterguy in forum Excel General
    Replies: 13
    Last Post: 02-28-2020, 08:54 AM
  3. [SOLVED] Match criteria (year) and obtain averages and sums
    By student789 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-05-2019, 03:12 AM
  4. [SOLVED] Year-to-Date Sums and Averages with different start dates
    By rachelglusk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-29-2015, 07:13 PM
  5. Replies: 4
    Last Post: 03-13-2015, 05:34 AM
  6. Replies: 4
    Last Post: 06-27-2014, 05:57 PM
  7. [SOLVED] Cross-Year averages by Quarter
    By Oxenstierna in forum Excel General
    Replies: 5
    Last Post: 05-03-2012, 11:43 AM

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