Slimming Chart

[dahowarduk]

In column A is the date.
In column B is my actual weight up to today (say up to day 50)
In column C is my target weight up to day 150.

Today I want a chart which shows B1 to B50 together with C1 to C150.
B51 to B150 currently contain the value zero.

Tomorrow I want a chart which shows B1 to B51 together with C1 to C150

The following day I want a chart which shows B1 to B52 together with C1 to C150, and so on.

I can achieve this by (each day) editing the chart via SELECT DATA ~ EDIT DATA ~ and then altering the data range in column B by 1 extra, for each day.

How can I obtain an updated chart automatically?

 

[JoePublic]

Do B51:B150 contain formulas that link to somewhere else?

 

[dahowarduk]

Yes they do.
The calculation is based on (starting weight - target weight) /( Length of days of diet) all multiplied by the day so far of the diet

 

[JoePublic]

Then the simplest options would be to return an error rather than 0 for days you don't want plotted (I'm assuming you have a line chart). Otherwise you can set up a named range that uses countif to only cover values that are greater than 0. If you post a sample workbook, we can help with that.

 

[dahowarduk]

Thanks. Here is the file. If you could not only fix it but show me how I'd be grateful.

 

[JoePublic]

The simplest option since you have the calculation columns hidden anyway, is to change the formula in column J to:
=IF(G18=0,NA(),G18+(H18+(I18/16))/14)
then simply alter the chart series for Actual to use all the rows you want, The #N/A values won't plot.

 

[dahowarduk]

Thanks. That worked fine, and my problem is fixed.