Line graphs where data comes from formulas.

[Skibee]

I am preparing a spreadsheet for use by a colleague and am trying to cut down
on the work they will have to do.

I have a spreadsheet with 52 tabs (representing each week of the year).
Information is entered weekly regarding customer sales and formulas calculate
costs of production and sales vs budget.
Another tab is called ‘graph data’. Relevant information from the 52 tabs
is collected on this sheet, some extra calculations are performed and then a
series of line graphs are compiled.

One row of excel, lets say row 6 contains information for the horizontal
axis. So in cell
B6 enter Wk1, C6 enter Wk2, D6 enter Wk3 etc until BA=Wk 52

Row 7 is to contain the data which forms the line on the line graphs.

I want the information for all the data for the 52 weeks in row 7 to be
picked up from elsewhere in the worksheet (ie the data in row 7 will be a
formula, such as B7=Z7, which will be information from the relevant weekly
tab (say Wk1) on which a further calculation has been performed ).

I want to enter the formulas for all 52 weeks – this way my colleague will
only have to complete the data on the relevant weekly tab, which will fire
through to the Graph Data tab and eventually to the correct cell on row 7 for
inclusion in the graph.

Problem
When I enter the formulas and build the graph, the line plummets down to a
scale of 0 for weeks for which there is currently no data (because those
weeks have not yet occurred). Is there any way of displaying the graph so
that the 52 weeks appear along the horizontal axis (right from week 1) and
the line on the graph only displays for those weeks for which information
exists (ie the formulas which currently display a value of 0 do not display.

The only way I have found round this so far is to leave my colleague to
enter the formula for the graphs each week; but the correct formula is not
always picked up and I was trying to find a way round this.

Sorry to be so long winded. Help appreciated – even if only to tell me to
stop banging my head against a brick wall!

 

[Bernard Liengme]

1) replace the empty cells with =NA(); this displays as #N/A which the chart
engine ignores
OR
2) click the chart, use Tools|Options and on the chart tab specify how empty
cells are to be treated
best wishes

 

[Skibee]

Thanks Bernard
My problem is that the cells are not empty - they contain a formula , the
value of which is 0 until the information becomes available. If I delete the
formulas, I am reliant on the user picking up the correct cells from
elsewhere in the worksheet to update the graphs each week - and I am trying
to avoid this as the wrong cells are sometimes picked up.

I do realise that I may be asking the impossible - but thought I'd post
before giving up - as my Excel level is only beginner to moderate.

Liz

 

[Bernard Liengme]

Just change the formulas
Suppose you have =my-formula
Replay this by =IF(my_test,my_formula,NA())
where my_test is something like B10>0 or B10<>"" .... some way to test if
the formula should be used or if NA() should be used.

Send me a file (remove TRUENORTH. from my email) and I will show you how
best wishes

 

[Skibee]

Bernard - that is so kind of you. Your email does not display in anything I
can see (can't find TRUENORTH. anywhere). Have tried clicking on your http
address but can't find email there either - could you send it through and
then I'll attach the file.
Many, many thanks
Liz

 

[Skibee]

Hi Bernard

I've managed to do it - following your advice and instructions. So, just to
say thank you very much for taking the time to help me and showing me
patience!
I've used the site a couple of times before, but always feel a bit nervous
that a combination of my lack of knowledge and jargon will irritate those
trying to help - thank you for not intimidating me!

Many, many thanks

Liz

 

[Bernard Liengme]

You are most welcome;Tkx for feedback