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

E

Excel785

New Member
Joined
Mar 6, 2019
Messages
9
=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)|
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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 />
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
Eric W said:
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.
Click to expand...

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.
 
Upvote 0
DanteAmor said:
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

ABCDEFG
c6
c13
c20
c27
c34
c41
c48
c55
c62
c69

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]DATE[/TD]
[TD="align: right"]04-ene[/TD]

[TD="bgcolor: #FFFF00"]DAYS[/TD]
[TD="bgcolor: #FFFF00"]Sheet2 Cell[/TD]
[TD="bgcolor: #FFFF00"]VALUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]AVERAGE[/TD]
[TD="align: right"]6.75[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"]0[/TD]

</tbody>

Formulas
CellFormula
B2=AVERAGE(INDIRECT("G2:G" & MATCH(DAY(B1),E1:E32)))
G2=INDIRECT("Sheet2!" &F2)

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

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.
 
Upvote 0
Excel785 said:
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.
Click to expand...


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.
 
Upvote 0
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 $).
 
Upvote 0
Excel785 said:
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.
Click to expand...

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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Pairwise Comparison Table
Replies
4
Views
356
Samgraphics
S
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
702
excelnewb221
E
willow1985
  • Solved
VBA Select all rows from active cell (selected cell) on table to 2nd last row
Replies
3
Views
253
rlv01
R
M
  • Question Question
Dynamic Cell Editing from Another Sheet
Replies
3
Views
407
Cubist
Cubist
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F

Forum statistics

Threads
1,223,526
Messages
6,172,832
Members
452,483
Latest member
Johnstone

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top