Formula has more than 8192 characters. Reduce Sheet reference or formula

[Excel785]

=IF(DAY($B$1)=1|'Sheet2'!$C$6|
IF(DAY($B$1)=2|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13)|
IF(DAY($B$1)=3|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13|'Sheet2'!$C$20)|
IF(DAY($B$1)=4|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13|'Sheet2'!$C$20|'Sheet2'!$C$27)|
IF(DAY($B$1)=5|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13|'Sheet2'!$C$20|'Sheet2'!$C$27|'Sheet2'!$C$34)|
IF(DAY($B$1)=6|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13|'Sheet2'!$C$20|'Sheet2'!$C$27|'Sheet2'!$C$34|'Sheet2'!$C$41)|
IF(DAY($B$1)=7|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13|'Sheet2'!$C$20|'Sheet2'!$C$27|'Sheet2'!$C$34|'Sheet2'!$C$41|'Sheet2'!$C$48)|
IF(DAY($B$1)=8|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13|'Sheet2'!$C$20|'Sheet2'!$C$27|'Sheet2'!$C$34'|Sheet2'!$C$41|'Sheet2'!$C$48|'Sheet2'!$C$55)|
IF(DAY($B$1)=9|AVERAGE('Sheet2'!$C$6|'Sheet2'!$C$13|'Sheet2'!$C$20|'Sheet2'!$C$27|'Sheet2'!$C$34'|Sheet2'!$C$41|'Sheet2'!$C$48|'Sheet2'!$C$55|'Sheet2'!$C$62)|

 

[Excel785]

The formula I posted starting with AVERAGE is an array formula. In order to get it to work properly, you have to tell Excel that it is an array formula. To do so, when you enter it in the formula bar, when you are done typing it in, or pasting it in, hold the Control and Shift keys down, then press Enter. Or if it's already in a cell, select that cell, press F2 to edit it, then hold the Control and Shift keys down and press Enter. If you've done it right, you'll get {} around the formula.

The way it works is by using OFFSET to identify the cells you want to average. B1 has the date you want, so DAY(B1) gives you the day of the month, let's say it's the 3rd of the month. INDIRECT converts "1:"&DAY(B1) into a range of 1:3, and ROW returns an array of {1,2,3}. We subtract 1 from the array giving {0,1,2}, then multiply by 7 giving {0,7,14}. Then using OFFSET, we start at $C$6 and add {0,7,14} giving C6, C13, and C20. SUBTOTAL gets the values of those cells, and AVERAGE averages them.


The SUMPRODUCT formula might be easier since it does not require the Control+Shift+Enter.

=SUMPRODUCT(Sheet2!C6:C216,--(MOD(ROW(Sheet2!C6:C216),7)=6),--(ROW(Sheet2!C6:C216)<=DAY(B1)*7))/DAY(B1)

It starts with the full range where your values could be: C6:C216.

The next section excludes any values if they are not on a row which leaves a remainder of 6 when dividing by 7 (6, 13, 20, etc.).

The next section excludes any values that are on rows above the day * 7, so if the day is 3, it'll ignore anything over 21.

Then it sums up what's left, and divides by the number of days.


If you are summing up multiple ranges, you will need to use the proper relative/absolute references for your ranges (with or without the $).

Thank you for explaining the formula. That is great! I will try the SUMPRODUCT one too.