Hi all



Whenever I try to plot an XY Scatter Graph where the x-axis is Time related and the result of a formula the graph doesn't display correctly. Instead of using the time formatted field as specified, it just numbers the data points sequentially on the x-axis. When I hover the cursor over a data point, I see ' "12/11/12 06:00" (1,550) ' instead of ' "12/11/12 06:00" (12/11/12 06:00,550) '.

Investigation has resulted in the discovery that, if I cut the data and paste the values only and remove all as yet unused formulas from the rest of the data range, the graph displays correctly. This leads me to believe that the formulas are the problem. Most are 'lookup' formulas but have even tried simple copy data from another cell formulas (=b2) but if the original data is the result of a formula ('lookup'), the result is the same.

I wish to create dynamic graphs that change as new data is added and this problem is preventing this.

Any help anyone can give is appreciated.

Thanks

Hi
perhaps post a small sample sheet of your data?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for your response.

The first attachment shows the graph as it should look. This has been achieved by cutting and pasting to separate different types of data manually instead of using the formulas to do this.

The second attachment shows the data itself. ‘All Reading’ are separated into their different types (‘AM’,’ PM’, ‘Post Exercise’ and ‘Routine’ for both AM and PM reading). This is to enable further analysis of the different types of data independently.

The third shows a graph where the ‘Routine Readings’ data is not where it should be. Only one data point is present at the second ’00:00’ point as this signifies ‘1’ on the x-axis. It also shows (1,550) instead of (10:40,550) for a correctly placed data point.

Hope this helps.

Attached Images

	Excel 1 - good graph.jpg (37.7 KB, 11 views)
	Excel 2 - Data.jpg (59.6 KB, 10 views)
	Excel 4 - Bad Graph.jpg (36.8 KB, 9 views)

Please add a sheet not a picture - Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi Pecoflyer,

Sorry for the misunderstanding.

I've attached the workbook as there's nothing sensitive or personal about it.

The previously attached screen shots of the graphs might help as they show how the graph should look.

Thanks again

Ady

Oh, forgot to mention, this version was created and saved as an Office97-2003 .xls version as I tried re-creating it at work with their older version of Office instead of my 2007 .xlsx version but the result was the same. Thanks

Attached Files

Eades - Peak Flow Diary (with formulas).xls (149.5 KB, 10 views)

I have discovered that the problem is not with the lookup part of the formula itself, its a problem with the part of the formula that allow for the facility to only display data if it exits.

It doesn't like 'IF' no data exists, display "" (empty cell). If I repace it with 'IF no data exists, display 0, the data is displayed correctly on the graph.

This isn't ideal but it's progress!

I've attached a trail sheet to show what I'm talking about. Hope it helps!

Thanks

Attached Files

Book1.xls (30.5 KB, 8 views)