Graph problem

Eric1066 · #1 07-30-2009, 10:21 AM

Hi,

Thx for all the help on the previous enquiry! new problem now!

I have a column with weekly dates and a column of turnover in £ i would like to see this displayed as a graph! but i do not want to have to generate the damn thing manually every week, I want a graph that updates as and when I type more dates and turnover amounts.

Is this possible? as having to generate the graph every week adding the extra cells manually seems stupid to me!

I seem to get close to this but it always looks wrong, firstly as I have already put dates going on five years ahead I get a graph with hundreds of dates with the line at 0 and with all my relevant data being squashed together you cant even read it, but, curiously, I also get dates on the graph that are not even in the table?? Why doesn't it just simply follow the pattern in the cells. I mean, why is it inventing stuff up? aaargghhh!! This is driving me nuts!

All I want is a nice looking graph that fills the area for the dates used and as and when I add more data for it to re-adjust within its window automatically. I feel like I'm asking too much!

Any help, again, greatly appreciated!!
Eric1066

Palmetto · #2 07-30-2009, 10:53 AM

See this link for Dynamic and Interactive Charts

Eric1066 · #3 07-31-2009, 12:36 PM

Thx for the response and I have learned a lot of Dynamic Charts but I am still having trouble and have been unable to find the solution.

I have 2 columns M & N
Week Ending (as a Date) & Turnover (£)

My chart is to plot turnover against the dates with the chart updating automatically. This I have figured out in the following manner:
I have defined 2 names:
Xvalues =OFFSET(Weekly_Totals!YValues,0,-1)
YValues =OFFSET(Weekly_Totals!$N$2,0,0,COUNTA(Weekly_Totals!$N:$N)-1,1)

The series formulas as: =SERIES(Weekly_Totals!$N$1,Weekly_Totals!XValues,Weekly_Totals!YValues,1)

The Problem:
My Turnover column (N) must be empty for the chart to work!! This would be fine if this column was the data entry column, but it is not, this column has a =sum formula as it is a summation of multiple streams of turnover and as these formulas are showing £0.00 it plots hundreds of £0.00 on the graph!

Any help greatly appreciated!!
Eric1066

Andy Pope · #4 08-01-2009, 07:15 AM

post your workbook

Eric1066 · #5 08-01-2009, 06:55 PM

Hi,

I have attached the file. Thanks for the help!

Andy Pope · #6 08-02-2009, 06:28 AM

Add another named range to determine first occurance of zero.
Call it TAKING

=MIN(IF(Sheet1!$O$2:$O$53=0,ROW(Sheet1!$O$2:$O$53),COUNTA(Sheet1!$N:$N)+1))

then revise the formula for YVALUES

=OFFSET(Sheet1!$N$2,0,0,TAKING,1)

Eric1066 · #7 08-02-2009, 01:50 PM

Hi,

Thx for the reply but that doesn't work. It does stop graphing the zeroes but it only graphs the first two weeks of data, so it has been reduced to a static chart rather than a dynamic one. If you enter week3 data the chart doesn't update.

WHER · #8 08-02-2009, 02:17 PM

Is this what you're looking for?

Eric1066 · #9 08-02-2009, 03:22 PM

thx, but no. its not about the zero's themselves but the empty graph positions. In this example there is a mere 53 lines so it doesn't seem as bad but in my real workbook there are over 1000 lines. The graph has to update completely dynamically and only show what is relevant.

Basically I need it to ignore any cells that have 0.00 or NA or anything that isnt a cash amount and to show only the latest data on the chart.

As this works fine if the cells were empty is there a function that puts a result from a sum into a different cell without that cell containing anything?

For example,
A1 has £50
B1 has £50
C1 is the =SUM of A1 & A2 ->£100
D1 is a function/formula that states if there is anything in C1 to put the result into E1. Therefore the figure '£100' would just appear in E1

I think something like this would solve the problem as the E column will technically be empty unless there is a figure in the C columns sums. If you get what I mean.

I have tried the opposite of this with an IF function to display "" if the cell is equal to 0 and although the cell seems empty the very Formula in the cell still disrupts the chart. It has to be empty or a function in the graph to ignore everything in the cell unless it is greater than 0.

WHER · #10 08-02-2009, 05:36 PM

Second attempt.

Eric1066 · #11 08-02-2009, 06:28 PM

Yep, thats the one! Thank you so much!

I see that you also sorted the problem with the excess graph points on the X axis by turning the dates into text, is there a quick way I can convert all those lines into text without having to add the ' manually? I tried formatting the cells to text but I got 38593 for 30/08/09.

Thank you again!
Eric1066

WHER · #12 08-02-2009, 08:06 PM

In M2:

Code:

=DAY(G2)&"/"&MONTH(G2)&"/"&YEAR(G2)

Andy Pope · #13 08-03-2009, 03:59 AM

That's because the TAKING named range was based on colulmn O for some reason.

TAKING: =MIN(IF(Sheet1!$N$2:$N$53=0,ROW(Sheet1!$N$2:$N$53),COUNTA(Sheet1!$N:$N)+1))

YVALUES: =OFFSET(Sheet1!$N$2,0,0,TAKING-2,1)

For the x axis problem just use the Chart Options to set the Axes to Category rather than Automatic.

Welcome to the Excel Forum

Forum Statistics