Forum Discussion
Using AVERAGEIF with =TODAY
- Have a look at the INDIRECT worksheet function. If I understand your setup, it might look like this:
=AVERAGEIF(INDIRECT($H$31&"34"):INDIRECT($F$31&"34"),A12,INDIRECT($H$31&"40"):INDIRECT($F$31&"40"))
Reference cells are A30:H31. I use them to help manual entry without having to use search functions or scroll all over the spreadsheet. D31 is =TODAY. E31 is =MATCH to return column number. F31 is =SUBSTITUTE(ADDRESS(1,E31,4),"1","") to give me the column letters for manual entry. I couldn't figure out how to do this directly off of the date, so I just had one cell reference the next, reference the next, etc. The next two cells are just to help me identify which cell to use as the start of the range depending on how many days I want to average.
Cells with AVERAGEIF are B3-B27.
B3: =AVERAGEIF($34:$34,A3,$40:$40) This averages my year; works well.
B12: =AVERAGEIF($GY$34:$JG$34,A12,$GY$40:$JG40) This averages the last two months. It works well, but I would love the portions "GY","JG" to autofill. Those are the columns that represent the applicable dates (for today, as I'm authoring this). As it stands I have to go in and manually change those column values to correspond with the day's date.
Is there a way to have my AVERAGEIF either reference TODAY directly, or to refer to the text of some of my reference cells D31:H31?
=AVERAGEIF(INDIRECT($H$31&"34"):INDIRECT($F$31&"34"),A12,INDIRECT($H$31&"40"):INDIRECT($F$31&"40"))