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>
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
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:

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,191
Members
416,079
Latest member
lizziebee

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
Top