Dynamic Chart help Excel 2007

Diane Parker

New Member
Joined
Jul 12, 2012
Messages
3
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))

ADCIDCJDCKDCLDCMDCNDCODCP
8/21/20198/22/20198/23/20198/24/20198/25/20198/26/20198/27/2019Weekly Avg
3319.019.118.819.519.519.619.519.3
4419.319.519.719.719.920.220.319.8
52120.621.021.020.921.521.621.821.2
62220.320.120.320.420.520.520.820.4
722.322.322.322.322.322.422.622.422.4
820.720.720.620.820.720.620.620.320.6
920.820.820.821.021.221.321.521.521.2
1021.821.822.222.021.922.022.322.422.1
1120.820.820.520.420.320.921.121.220.7
1219.619.619.819.819.919.719.820.019.8
1324.024.023.823.623.523.423.623.923.7
1425.425.425.225.024.924.825.325.325.1
2620.820.820.920.920.921.121.221.321.0

<tbody>
</tbody>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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