Forum Discussion
Help with Excel formulas
I have two columns let's called 2015 and 2016, each column has 365 rows from day 1 to day 365. Each day I have a different value. In another tab I input in cell A1 the year in A2 the month in A3 the ...
If you wanted to consider changing the way you enter the start and end dates, I have a solution for you. If A1 is your start date, as 1/1/2015, and A2 is your end date as 1/3/2015, and your data is on Sheet1, with cell A1 being 2015, and the data is immediately below, the formula below will get you what you seem to want. it will accept data to row 367 (to allow for leap year), and is already set up to allow for new years up to column "U" (that's 2035 if you add consecutive years).
=AVERAGE((INDEX(Sheet1!A1:U367,DAYS(A1,"1/1/"&YEAR(A1))+2,MATCH(YEAR(A1),Sheet1!A1:U1,0))):(INDEX(Sheet1!A1:U367,DAYS(A1,"1/1/"&YEAR(A1))+2+DAYS(A2,A1),MATCH(YEAR(A1),Sheet1!A1:U1,0))))