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)))
Bookmarks