1

So this has been a challenging problem for me. I am trying to get Excel to do an average over a period of time based on what is in the column header.

So here's the column header that I'm talking about:

where the result should go

This is where the (Result) should go.

And then here is my original data (Nut Cost)

original data

So the intent is that I can take the average for Peanuts between 201604 and 201703 based on the header '201604-201703 Avg'.

My thought is to use something like this: 

=averageifs('Nut Cost'!$B$2:$P$5,'Nut Cost'!$A$1:$P$1,left('Result'!B2,6):mid('Result'!B2,8,6),'Nut Cost'!$A$2:$A$5,'Result'!$A2)

My other thought is to use something like this:

=SUMIFS('Nut Cost'!$B$2:$P$5,'Nut Cost'!$A$1:$P$1,">="&LEFT('Result'!B2,6),'Nut Cost'!$A$1:$P$1,"<="&MID('Result'!B2,8,6),'Nut Cost'!$A$2:$A$5,'Result'!$A2)

which would be divided by a countif with similar style.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
resonance1
  • 97
  • 14

1 Answers1

2

Use INDEX to return the correct row to the AVERAGEIFS()

=AVERAGEIFS(INDEX('Nut Cost'!$B$2:$P$5,MATCH('Result'!$A2,'Nut Cost'!$A$2:$A$5,0),0),'Nut Cost'!$B$1:$P$1,">=" & left('Result'!$B$1,6),'Nut Cost'!$B$1:$P$1,"<=" & mid('Result'!$B$1,8,6))

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I tried to use this code translated to the actual workbook I'm using and it doesn't work. I get a #VALUE error. If I remove the 0 for the column_num in the index function I get a #REF error. I'm not sure what the problem is since when I evaluate the formula it seems to be resolving correctly until when it does the final calculation. – resonance1 Aug 17 '18 at 20:11
  • It works on the data you presented, as to why it does not work on your actual data, I can't begin to figure out as I do not have access to your data. – Scott Craner Aug 17 '18 at 20:12
  • What is the actual formula you are trying? – Scott Craner Aug 17 '18 at 20:14
  • =AVERAGEIFS(INDEX('PMPM By Class'!$B$4:$AX$24,MATCH('Class PMPM Analysis'!$A3,'PMPM By Class'!$B$4:$B$24,0),0),'PMPM By Class'!$C$4:$AX$4,">=" & LEFT('Class PMPM Analysis'!B$2,6),'PMPM By Class'!$C$4:$AX$4,"<=" & MID('Class PMPM Analysis'!B$2,8,6)) – resonance1 Aug 17 '18 at 20:20
  • Change the first reference to `$C$4:$AX$24` The number of columns needs to match those of the rest of the AVERAGEIFS() – Scott Craner Aug 17 '18 at 20:45
  • I get a #DIV/0! error when I do that and the values are not 0 – resonance1 Aug 17 '18 at 21:28
  • I got back around to doing this and your formula is correct. I had a data type issue with a couple of the column headers. Thank you for your help. – resonance1 Aug 22 '18 at 19:55