G
Guest
Good morning all.
I haven't really done a huge amount on charting, so I'm sure you'll think
that these questions are a bit simplistic, but here goes!
I plot a chart from a worksheet that contains three columns of data - the X
axis contains week numbers, and is in B29:B54. Actual hours are entered in
F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the
form: =SUM($F$29:F29)
So, each week, in order to plot another week's data, I have to copy this
cumulative formula down by one cell, which, considering I have a lot of these
reports, is a bit time consuming.
If I replace my cumulative formula with something that displays a zero or a
label if there is no corresponding weekly actual, the cumulative chart line
plummets down to zero, which makes the charts look unprofessional. If I
replace it with something that picks up the previous week's cumulative value
if there is no corresponding weekly actual, the cumulative chart line goes
across at a value equal to the latest cumulative, which also doesn't look too
good.
So, the question is, how, in this week for example, can I have my chart area
as b29:B54 (x-axis);f29:f54 (actuals); g29:f40 (cumulatives), but next week
extend
my cumulative plot to g29:f41 (i.e. extend it down by one week)?
Eventiually, I'd like to etend this idea to plotting a rolling 12 week
chart, so to start, it would plot b29:B38 (x-axis);f29:f38 (actuals); g29:f38
(cumulatives) then the next week, b30:B39 (x-axis);f30:f39 (actuals); g30:f39
(cumulatives) and so on, but one thing at a time.
If anyone has any good pointers as to how I might achieve some or all of the
above, I'd be extremely grateful.
Regards & thanks in advance.
Pete
I haven't really done a huge amount on charting, so I'm sure you'll think
that these questions are a bit simplistic, but here goes!
I plot a chart from a worksheet that contains three columns of data - the X
axis contains week numbers, and is in B29:B54. Actual hours are entered in
F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the
form: =SUM($F$29:F29)
So, each week, in order to plot another week's data, I have to copy this
cumulative formula down by one cell, which, considering I have a lot of these
reports, is a bit time consuming.
If I replace my cumulative formula with something that displays a zero or a
label if there is no corresponding weekly actual, the cumulative chart line
plummets down to zero, which makes the charts look unprofessional. If I
replace it with something that picks up the previous week's cumulative value
if there is no corresponding weekly actual, the cumulative chart line goes
across at a value equal to the latest cumulative, which also doesn't look too
good.
So, the question is, how, in this week for example, can I have my chart area
as b29:B54 (x-axis);f29:f54 (actuals); g29:f40 (cumulatives), but next week
extend
my cumulative plot to g29:f41 (i.e. extend it down by one week)?
Eventiually, I'd like to etend this idea to plotting a rolling 12 week
chart, so to start, it would plot b29:B38 (x-axis);f29:f38 (actuals); g29:f38
(cumulatives) then the next week, b30:B39 (x-axis);f30:f39 (actuals); g30:f39
(cumulatives) and so on, but one thing at a time.
If anyone has any good pointers as to how I might achieve some or all of the
above, I'd be extremely grateful.
Regards & thanks in advance.
Pete