Dynamic Chart help Excel 2007

[Diane Parker]

Need help taking the last data point (DCP) off several dynamic linear charts. The last data point is the weekly average and it often creates a blip at the end of the data. No VBA please. Thank you so much.

=OFFSET(Current!$A$1,25,MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0),1,MATCH(Current!$A$174,Current!$B$1:Current!$XEA$1,0)-MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0))

[TABLE="width: 803"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]DCI[/TD]
[TD]DCJ[/TD]
[TD]DCK[/TD]
[TD]DCL[/TD]
[TD]DCM[/TD]
[TD]DCN[/TD]
[TD]DCO[/TD]
[TD]DCP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]8/21/2019[/TD]
[TD]8/22/2019[/TD]
[TD]8/23/2019[/TD]
[TD]8/24/2019[/TD]
[TD]8/25/2019[/TD]
[TD]8/26/2019[/TD]
[TD]8/27/2019[/TD]
[TD]Weekly Avg[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]19.0[/TD]
[TD]19.1[/TD]
[TD]18.8[/TD]
[TD]19.5[/TD]
[TD]19.5[/TD]
[TD]19.6[/TD]
[TD]19.5[/TD]
[TD]19.3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]19.3[/TD]
[TD]19.5[/TD]
[TD]19.7[/TD]
[TD]19.7[/TD]
[TD]19.9[/TD]
[TD]20.2[/TD]
[TD]20.3[/TD]
[TD]19.8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]21[/TD]
[TD]20.6[/TD]
[TD]21.0[/TD]
[TD]21.0[/TD]
[TD]20.9[/TD]
[TD]21.5[/TD]
[TD]21.6[/TD]
[TD]21.8[/TD]
[TD]21.2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]22[/TD]
[TD]20.3[/TD]
[TD]20.1[/TD]
[TD]20.3[/TD]
[TD]20.4[/TD]
[TD]20.5[/TD]
[TD]20.5[/TD]
[TD]20.8[/TD]
[TD]20.4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]22.3[/TD]
[TD]22.3[/TD]
[TD]22.3[/TD]
[TD]22.3[/TD]
[TD]22.3[/TD]
[TD]22.4[/TD]
[TD]22.6[/TD]
[TD]22.4[/TD]
[TD]22.4[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]20.7[/TD]
[TD]20.7[/TD]
[TD]20.6[/TD]
[TD]20.8[/TD]
[TD]20.7[/TD]
[TD]20.6[/TD]
[TD]20.6[/TD]
[TD]20.3[/TD]
[TD]20.6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]20.8[/TD]
[TD]20.8[/TD]
[TD]20.8[/TD]
[TD]21.0[/TD]
[TD]21.2[/TD]
[TD]21.3[/TD]
[TD]21.5[/TD]
[TD]21.5[/TD]
[TD]21.2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]21.8[/TD]
[TD]21.8[/TD]
[TD]22.2[/TD]
[TD]22.0[/TD]
[TD]21.9[/TD]
[TD]22.0[/TD]
[TD]22.3[/TD]
[TD]22.4[/TD]
[TD]22.1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]20.8[/TD]
[TD]20.8[/TD]
[TD]20.5[/TD]
[TD]20.4[/TD]
[TD]20.3[/TD]
[TD]20.9[/TD]
[TD]21.1[/TD]
[TD]21.2[/TD]
[TD]20.7[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]19.6[/TD]
[TD]19.6[/TD]
[TD]19.8[/TD]
[TD]19.8[/TD]
[TD]19.9[/TD]
[TD]19.7[/TD]
[TD]19.8[/TD]
[TD]20.0[/TD]
[TD]19.8[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]24.0[/TD]
[TD]24.0[/TD]
[TD]23.8[/TD]
[TD]23.6[/TD]
[TD]23.5[/TD]
[TD]23.4[/TD]
[TD]23.6[/TD]
[TD]23.9[/TD]
[TD]23.7[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]25.4[/TD]
[TD]25.4[/TD]
[TD]25.2[/TD]
[TD]25.0[/TD]
[TD]24.9[/TD]
[TD]24.8[/TD]
[TD]25.3[/TD]
[TD]25.3[/TD]
[TD]25.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]20.8[/TD]
[TD]20.8[/TD]
[TD]20.9[/TD]
[TD]20.9[/TD]
[TD]20.9[/TD]
[TD]21.1[/TD]
[TD]21.2[/TD]
[TD]21.3[/TD]
[TD]21.0[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

I believe this will do it:

=OFFSET(Current!$A$1,25,MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0),1,MATCH(Current!$A$174,Current!$B$1:Current!$XEA$1,0)-MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0)-1)

The -1 in the location shown will remove 1 from the width of the block specified by the original OFFSET formula.


I am assuming that Current!$A$174 contains "Weekly Average" instead of the final date of the week and A172 contains he fir date of the week.

 

[Diane Parker]

Thank you for responding ....I sent you a PM.

 

[pbornemeier]

If the Weekly Average column (DCP) is left in and the next week's data starts in column DCQ then I don't see a simple way to show (or compare several months of that data without the weekly average Blip unless you copy the data, remove the weekly average columns and plot the "cleaned" datasets. This could be done manually, or with VBA.

Something I just tried (in Excel 2016) was to delete the text titles (Weekly Avg) and leave the average values in the range I was plotting. Excel skipped those points and only plotted the columns with dates at the top. I don't know if earlier versions of excel do this.