Average data in Column that changes location

[cam339]

Hi,

I have reports that span from Column A up to AQ in excel. On these reports I need to average based off the group name and response time but the column the response time is located on changes from report to report. Group name is always in the same column so is there a way I can fix the formula so it can pick up that the response time based off its header and not the column?

This is the formula I use at the moment on a template I have. AVERAGEIF('Data'!$M:$M,"Group",'Data'!$AQ:$AQ) The problem I have is that I need to update the $AQ to where ever the response time is now located. Any way around this?

Thank you.

 

[Aladin Akyurek]

Is Group always in column M? And what is the header of the column housing the response times?

 

[cam339]

Hi,

Group is always in Column M. The header for the response time is "response time".

 

[Aladin Akyurek]

Try...

=AVERAGEIFS(INDEX(Data!$A:$AQ,0,MATCH("response time",INDEX(Data!$A:$AQ,1,0),0)),Data!$M:$M,"Group")

1. If you need using AVERAGEIF...

=AVERAGEIF(Data!$M:$M,"Group",INDEX(Data!$A:$AQ,0,MATCH("response time",INDEX(Data!$A:$AQ,1,0),0)))

2. The header value "response time" is expected to occur in the top row.

3. You can extend the range beyond column AQ if needed.

 

[cam339]

That worked. Thank you for your assistance Aladin Akyurek.

 

[Aladin Akyurek]

That worked. Thank you for your assistance Aladin Akyurek.

You are welcome.