Forum Discussion
Using AVERAGEIF with =TODAY
- Sep 24, 2024Have 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"))
- BryanW155Oct 01, 2024Copper ContributorI 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.