Re: Formula to search for date and extract average data to the beginning of year

There may be an easier way to do this, but this is the best I can come up with to make it truly dynamic.

I will only show you how to come up with the Average formula for the dynamic date range of (First month of the year - latest month listed in your table).

First off here is the formula you can use: *A3=AVERAGE(INDIRECT("r" & ROW(A3)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A3)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE))*

Essentially what I am doing is using the INDIRECT formula to pull out the rows and columns we want to look at. Simplified it will read like this: *INDIRECT("RXCX:RYCY",FALSE)*

Where X and Y are the row and column references for the start date and end date of your range. The trick here is to dynamically pull your rows and columns and insert them into this formula. Let me break down each part of the formula:

1. The row reference: This is the easiest section of the formula because it's just looking at the row the formula lives in:*=ROW(A3)*

2. The First month of the year column: Here we just want to find the date in your headers which references the first month of the current year. We can use a simple match formula for this (This segment of the formula can be updated to show the last month of the previous year if you wanted to do that):*=MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0)*

3. Last we want to find the most recent month in your headers. We will use another match formula, taking the greatest value of all your headers (Excluding column A). You can extend the **bolded **part out further if you plan to have more headers in the future):*=MATCH(MAX($B$2: $Z$2),$B$2:$Z$2,0)+1*

I hope this makes sense.

If you delete the month of July in your attached spreadsheet then you receive these results:

A3=AVERAGE(INDIRECT("r" & ROW(A3)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A3)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE)) = **96.83**A4=AVERAGE(INDIRECT("r" & ROW(A4)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A4)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE)) =

**94.83**

A5=AVERAGE(INDIRECT("r" & ROW(A5)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A5)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE)) =

**95.17**

Which lines up exactly with your formula for the Avg of June.

Sincerely,

Max