General discussion

Excel Graph problem

I am trying to create an excel graph which will have the base data updated week by week through the year.

What I don't want is a graph where lines drop to zero part way through the year

I have found that if the cell being graphed is empty, this doesn't happen, but if the cell contains a formula that equals zero, it does.

I tried changing the formula along the lines of IF(value=0,"",value) but that didn't work.

Because the base data comes from many sources, I want to set up all the links for the base data at the start of the year, and not have to copy formulas week by week throughout the year.

I also don't want to have to go into every series in every graph every week, and alter the range to include 1 more column.

Is there a way to get around this problem?

Discussion is locked

Follow
Reply to: Excel Graph problem
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: Excel Graph problem
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Comments
- Collapse -
Write a macro.

The macro should copy the formulas into the empty rows. Then all you've got to do each week is click on button labeled something like "new week". That seems quite workable.

Kees

- Collapse -
An alternative

to Kees suggestion is to experiment with different graph types.

Do you have to have a line graph? What about an Area graph where the area underneath the line is filled in with a color or pattern. Then at the date of the latest data, the area just stops in a vertical line, and does not look strange at all.

Or could you use a Column graph? Any empty columns would show up as very thin zero amount lines on the X axis.

If neither of those suit you, (and they are not always the right kind), then Kees suggestions seems the best way.

Mark

- Collapse -
Excel Graphs

The problem with this is that there are several lines on each graph, and it might look a bit too confusing with multiple columns.

I think I may have to go down the macro route.

The other solution, which I am trying to avoid, because it's a bit tricky to set up, is to build a pivot table out of your data, and graph that.

You can have a column in the data to flag if data exists for that week, and have the pivot table ignore unflagged lines.

Then all you have to do is update the pivot table each week.

- Collapse -
Good luck

with the macro. Or the Pivot Table.

I have always struggled with Pivot Tables and I must get round to sorting myself out with them.

Mark

CNET Forums

Forum Info