+ Reply to Thread
Results 1 to 4 of 4

Create an array formula to obtain the average of an array of index and match function

  1. #1
    Registered User
    Join Date
    03-29-2016
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    7

    Create an array formula to obtain the average of an array of index and match function

    Good day Everyone!! I had a challenging problem in getting the average, Max & min of an array of data extract from a pool of table using index and match function.

    Currently i am stuffing too much of index and match function in my formula to pull out individual data to manually get the result.

    Can anyone advise me how i can create an array formula to get data for E11 to E13 without so many repetition as shown below?

    You can image the nightmare when there is an increase of data (Sample data layout) require to extract and obtain the average, max & min

    Alternatively you may refer to my attached for more info

    Thank you

    =AVERAGE(INDEX($D$2:$O$5,MATCH($D$31,$B$2:$B$185,0), MATCH(E10,$D$1:$O$1,0)), INDEX($D$2:$O$5,MATCH($D$31,$B$2:$B$185,0), MATCH(E11,$D$1:$O$1,0)),INDEX($D$2:$O$5,MATCH($D$31,$B$2:$B$185,0), MATCH(E12,$D$1:$LK$1,0)), INDEX($D$2:$O$5,MATCH($D$31,$B$2:$B$185,0), MATCH(E13,$D$1:$O$1,0)))
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Create an array formula to obtain the average of an array of index and match function

    Hello
    If you're willing to reference the cells E10:G13, then try the following array formula (Ctrl+Shift+Enter)for the A1-D1 values:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Same for MIN and MAX. Adjust the red range for the other criteria A3-D3 etc.

    DBY

  3. #3
    Registered User
    Join Date
    03-29-2016
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    7

    Re: Create an array formula to obtain the average of an array of index and match function

    Quote Originally Posted by DBY View Post
    Hello
    If you're willing to reference the cells E10:G13, then try the following array formula (Ctrl+Shift+Enter)for the A1-D1 values:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Same for MIN and MAX. Adjust the red range for the other criteria A3-D3 etc.

    DBY
    Hello DBY.

    You saved my day. Your solution is ingenious!! Thank you very much

  4. #4
    Registered User
    Join Date
    03-29-2016
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    7

    Re: Create an array formula to obtain the average of an array of index and match function

    Hi DBY

    If I want to obtain average for E10:G13 instead of E10:E 13, how should I do it as I received a error saying it is too large after I substitute E10:E13 with E10:G13

    Thanks

+ 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. [SOLVED] Index and Match Function across multiple array
    By Ray Park in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-04-2023, 03:28 AM
  2. [SOLVED] INDEX/MATCH Array within LARGE Function?
    By DZ217 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-12-2015, 11:18 AM
  3. Index match function without array formula for multiple rows.
    By markb141 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2014, 10:35 AM
  4. [SOLVED] Variable Row Array in Index Match Function
    By Lacaycer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2013, 11:45 AM
  5. Use Index/Match Function to Return Values from an Array
    By 00pumpkin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2013, 02:22 PM
  6. Index Match array equation with sub-array calculation
    By glebbo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2012, 03:04 AM
  7. Index/Match function inside an array
    By tittiot in forum Excel General
    Replies: 2
    Last Post: 01-20-2010, 09:48 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