Help with Excel formulas

[Edward R.]

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 day 1 and in A4 the day 2. Ok now in A5 I need to write the correct formula to take the criteria for these values and calculate the average from day x to day y from tab 2.

For example. Average from January 1 to January 3 2015.

Thank you!

[#2]

According to your description, I introduced a new column to represent a date (Month+Day/100) by a number. Then, apply the function "averageifs" to perform. However, since there are two years, I have an "if" to determine which column to be the result. You may refer to the sheet RS1 in the attachment. 

I don't like this kind of data structure. First, the column A actually representing the day in year 2018 (except 28/2/2016). Second, it is difficult to do calculation for the data. You see that there is an "if" to get the final result. It will be very complicated if there are more columns (years).

For this, I prepare the sheet RS2. In it, all the days are arranged in column A (Date Format d-mmm-yyyy). Then, applying "averageifs" to calculate the required average. 

Hope that this is helpful to you. 

[#3]

Hi Edward R,

The Array Formula that I have written works for multiple years.

=SUM(SUM(IF($B$1:$U$1=$W$2,$B$2:$U$367,0)*IF($A$2:$A$367+DATE($W$2,1,0)>=DATE($W$2,$X$2,$Y$2),1,0)),SUM(IF($B$1:$U$1>$W$2,$B$2:$U$367,0)*IF($B$1:$U$1<$W$3,1,0)),SUM(IF($B$1:$U$1=$W$3,$B$2:$U$367,0)*IF($A$2:$A$367+DATE($W$3,1,0)<=DATE($W$3,$X$3,$Y$3),1,0)))/SUM(SUM(IF($B$1:$U$1=$W$2,IF(ISNUMBER($B$2:$U$367),1,0),0)*IF($A$2:$A$367+DATE($W$2,1,0)>=DATE($W$2,$X$2,$Y$2),1,0)),SUM(IF($B$1:$U$1>$W$2,IF(ISNUMBER($B$2:$U$367),1,0),0)*IF($B$1:$U$1<$W$3,1,0)),SUM(IF($B$1:$U$1=$W$3,IF(ISNUMBER($B$2:$U$367),1,0),0)*IF($A$2:$A$367+DATE($W$3,1,0)<=DATE($W$3,$X$3,$Y$3),1,0)))

I have designed it for Numerical data, not for non-numerical data. Cells with non-numerical data should be kept blank.

The Start Year, Month and Day as well as the End Year, Month and day can be entered independently in cells W2 to Y3.

I have also changed the year (column A) to 1900, instead of 2018.

The formula is written for 20 columns (20 years), but can be extended.

Array Formulas are written with Control + Shift + Enter, instead of Enter. The Formula is in cell AA2 in the attached Excel File.

Refer to the attached Excel file for the formula.

Since you have only 2 years of data (2 columns), probably the formula may not be required for you for the current data. If the number of years increase, it will help you.

Vijaykumar Shetye,

Panaji, Goa, India

[#4]

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))))