Forum Discussion
Solved
Using AVERAGEIF with =TODAY
I have an AVERAGEIF formula that searches the data for a given hour, across an entire year. Works great. I would like that formula to also tell me the average across the last two months, and I would like it to update automatically as the year progresses and I enter new data. As it stands, I have to change the reference column manually (date's run across in columns).
Is there a way to make the column portion of AVERAGEIF change in response to the value of =TODAY?
Thanks!
- 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"))
4 Replies
- It would help to see a relevant portion of your sheet and to see the current formula.
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?
- 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"))
