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

 

[DanteAmor]

Try this.
Create an auxiliary column with the days from 1 to 31 (in my example column E), another auxiliary column with the reference cell (column F), in the auxiliary column G there is a formula to obtain the amount of the sheet2.
In cell B2 is the formula to obtain the average according to the day.


<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:32px;" /><col style="width:32px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">DATE</td><td style="text-align:right; ">04-ene</td><td > </td><td > </td><td style="background-color:#ffff00; ">DAYS</td><td style="background-color:#ffff00; ">Sheet2 Cell</td><td style="background-color:#ffff00; ">VALUE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">AVERAGE</td><td style="text-align:right; ">6.75</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >c6</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2</td><td >c13</td><td style="text-align:right; ">7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td><td >c20</td><td style="text-align:right; ">10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">4</td><td >c27</td><td style="text-align:right; ">8</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">5</td><td >c34</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">6</td><td >c41</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">7</td><td >c48</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">8</td><td >c55</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">9</td><td >c62</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">10</td><td >c69</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=AVERAGE(INDIRECT("G2:G" & MATCH(DAY(B1),E1:E32)))</td></tr><tr><td >G2</td><td >=INDIRECT("Sheet2!" &F2)</td></tr></table></td></tr></table> <br /><br />

 

[Eric W]

This array formula should work:

=AVERAGE(SUBTOTAL(9,OFFSET(Sheet2!$C$6,(ROW(INDIRECT("1:"&DAY($B$1)))-1)*7,0)))

confirm it in the formula bar by pressing Control+Shift+Enter.

 

[Eric W]

This non-array formula also works:

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

You may need to reformat the cell as General, since Excel will see the DAY function and reformat it as a date.

 

[Excel785]

Thank you! I tried the formula and I get a 0.

Maybe I should explain a little bit more what I am trying to do? I have a summary sheet and Sheet 2 with multiple column and rows for example columns C,I,O U, and 5 rows after every 7 row, counting from c6. c6c7,c8,c9 and c10 then i6,i7,i8,i9 and i10 and so on for O nd U columns. I am trying to average day 1, 2 and so on up to day 31. for each row of 1-5.
I am new to this forum, how does you formula work? Can you explain please? Thanks for all the help.

 

[Excel785]

This array formula should work:

=AVERAGE(SUBTOTAL(9,OFFSET(Sheet2!$C$6,(ROW(INDIRECT("1:"&DAY($B$1)))-1)*7,0)))

confirm it in the formula bar by pressing Control+Shift+Enter.

Thank you! I tried the formula and I get a 0.

Maybe I should explain a little bit more what I am trying to do? I have a summary sheet and Sheet 2 with multiple column and rows for example columns C,I,O U, and 5 rows after every 7 row, counting from c6. c6c7,c8,c9 and c10 then i6,i7,i8,i9 and i10 and so on for O nd U columns. I am trying to average day 1, 2 and so on up to day 31. for each row of 1-5.
I am new to this forum, how does you formula work? Can you explain please? Thanks for all the help.

 

[Excel785]

Try this.
Create an auxiliary column with the days from 1 to 31 (in my example column E), another auxiliary column with the reference cell (column F), in the auxiliary column G there is a formula to obtain the amount of the sheet2.
In cell B2 is the formula to obtain the average according to the day.


Sheet

	A	B	C	D	E	F	G
1	DATE	04-ene			DAYS	Sheet2 Cell	VALUE
2	AVERAGE	6.75			1	c6	2
3					2	c13	7
4					3	c20	10
5					4	c27	8
6					5	c34	0
7					6	c41	0
8					7	c48	0
9					8	c55	0
10					9	c62	0
11					10	c69	0

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 80px;"><col style="width: 80px;"><col style="width: 32px;"><col style="width: 32px;"><col style="width: 80px;"><col style="width: 80px;"><col style="width: 80px;"></colgroup><tbody>
</tbody>

Formulas

Cell Formula B2 =AVERAGE(INDIRECT("G2:G" & MATCH(DAY(B1),E1:E32))) G2 =INDIRECT("Sheet2!" &F2) <tbody> </tbody>

<tbody>
</tbody>

Maybe I should explain a little bit more what I am trying to do? I have a summary sheet and Sheet 2 with multiple column and rows for example columns C,I,O U, and 5 rows after every 7 row, counting from c6. c6c7,c8,c9 and c10 then i6,i7,i8,i9 and i10 and so on for O nd U columns. I am trying to average day 1, 2 and so on up to day 31. for each row of 1-5.
I am new to this forum, how does you formula work?
Thanks for all the help.

 

[DanteAmor]

Maybe I should explain a little bit more what I am trying to do? I have a summary sheet and Sheet 2 with multiple column and rows for example columns C,I,O U, and 5 rows after every 7 row, counting from c6. c6c7,c8,c9 and c10 then i6,i7,i8,i9 and i10 and so on for O nd U columns. I am trying to average day 1, 2 and so on up to day 31. for each row of 1-5.
I am new to this forum, how does you formula work?
Thanks for all the help.

You could upload a copy of your file, there you explain a couple of examples of what you have and what you expect from the resul, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Eric W]

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

 

[Excel785]

Thank you! I tried the formula and I get a 0.

Maybe I should explain a little bit more what I am trying to do? I have a summary sheet and Sheet 2 with multiple column and rows for example columns C,I,O U, and 5 rows after every 7 row, counting from c6. c6c7,c8,c9 and c10 then i6,i7,i8,i9 and i10 and so on for O nd U columns. I am trying to average day 1, 2 and so on up to day 31. for each row of 1-5.
I am new to this forum, how does you formula work? Can you explain please? Thanks for all the help.

That is actually very compact and powerful.
I tried again and it is working, but when it encounters DIV/0 it does not average. How can I correct that?
Is there a way to change the sheet name automatically when the month changes? The tab name changes with new month.

Thank you!