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 ...
- 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"))
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"))- I cannot express how thankful I am for this response. You have saved me about 2 hours of work every month just manually entering data. Formula works flawlessly exactly as you wrote it. Now if I could just get Square point of sale to compile this data natively we'd be all set, haha.
