Locate column and average range in it

[DaveTurton]

Hi


I have data similar to below. What I want to do is, for example, if any of the years (i.e. 2011-12) match the text in cell A1 then I want to display an average of the data under the specific year header


so if A1 contained "2011-12" the output would be 4828.75


Week 2011-12 2012-13 2013-14

2 4930 4430 0

3 4918 4531 0

4 4642 4448 0

5 4825 4454 0


Any ideas?


Thank you

 

[Hui]

Dave


If A1 has 2011-12

and the table is in A3:D7


Try: =AVERAGE(OFFSET(A4:A7,,MATCH(A1,B3:D3)))

refer: https://www.dropbox.com/s/t3u50jm4yc4cbmz/Dave_Average.xlsx

 

[DaveTurton]

Adapted it to my full range and match criteria on a different sheet and worked perfectly. Thank you very much.


Just two more things . I am also doing min and max, which is fine, simple swap out the average for either min or max. However is there a way to find min value which is greater than zero.


Also how could I find what week number each of the min and max occurred in?


Thank you again for your help.

 

[Hui]

Dave

Have you thought of using the Google Custom Search box at the top right of this and every page at Chandoo.org?


Give it a go, you'll be amazed what you find


ps:

=MIN(IF(OFFSET(A4:A7,,MATCH(A1,B3:D3,0))>0,OFFSET(A4:A7,,MATCH(A1,B3:D3,0)),)) Ctrl+Shift+Enter

=MAX(IF(OFFSET(A4:A7,,MATCH(A1,B3:D3,0))>0,OFFSET(A4:A7,,MATCH(A1,B3:D3,0)),)) Ctrl+Shift+Enter

Link above is updated with these as well

 

[DaveTurton]

Hi Hui


Apologies for not making full use of the sear function.


I have tried your formula, if there is a 0 in the column it returns 0 instead of the lowest value excluding zeros

 

[Hui]

Whoops forgot those 2 little rabbits


=MIN(IF(OFFSET(A4:A7,,MATCH(A1,B3:D3,0))>0,OFFSET(A4:A7,,MATCH(A1,B3:D3,0)),"")) Ctrl+Shift+Enter

 

[DaveTurton]

Thank you