Plotting elapsed time when calculated

  • Thread starter Thread starter Phil C
  • Start date Start date
P

Phil C

Hi folks

I have a x-y scatter graph with a log scale y-axis. I recently had to change
the x source data, now the data does not plot correctly. To give you an
idea, the y-axis is percentage (3 cycles of log going up to a maximum of
1000) and the x-axis is elapsed time in minutes going from 0-240. I have 6
data points plotted. Before I made the recent change the x-axis coordinates
(elapsed time values) were just a column of numbers (in Cells C36:C41),
formatted as Number. Rather than enter the (elapsed) time directly, I now
want Excel to calculate it (Time A - Time B) and use the result as the
x-coordinate.

B36:B41 now has time-of-day input (12:30, 13:15, etc), formatted as hh:mm,
and C36:C41 (formatted as Number) now contains formulae of the type:

=IF(B36="","",24*60*(B36-$C$14)).

The formula above does the time subtraction and converts the difference into
(decimal) minutes. C14 is a reference time, formatted as, er, time (hh:mm).
The formula apparently works fine (e.g. 12:30-10:30 = 120) but on the graph
all the data points are now crowded together at the left hand side. If I
hover the mouse pointer above individual data points I can see why (sort
of) ..

The second point (x-coord [in cell C37] is 150) indicates [Series "Plasma
curve" Point "150" (2,89.6)] where it should say [Series "Plasma curve"
Point "150" (150,89.6)]. Excel is seeing the "150" time value OK (if I
change the time of day in B37 the data label changes), but plots it as "2",
apparently because it is the second point in the list! Similarly, the third
data point (x coord = 180) on the graph is labelled as [Series "Plasma
curve" Point "180" (3,72.7)] when it should be [Series "Plasma curve" Point
"180" (180,72.7)].

Any ideas?? Formatting problem? BTW, the x-axis of the graph is also
formatted as Number.

Thanks in advance.
 
Phil said:
Hi folks

I have a x-y scatter graph with a log scale y-axis. I recently had to
change the x source data, now the data does not plot correctly. To
give you an idea, the y-axis is percentage (3 cycles of log going up
to a maximum of 1000) and the x-axis is elapsed time in minutes going
from 0-240. I have 6 data points plotted. Before I made the recent
change the x-axis coordinates (elapsed time values) were just a
column of numbers (in Cells C36:C41), formatted as Number. Rather
than enter the (elapsed) time directly, I now want Excel to calculate
it (Time A - Time B) and use the result as the x-coordinate.

B36:B41 now has time-of-day input (12:30, 13:15, etc), formatted as
hh:mm, and C36:C41 (formatted as Number) now contains formulae of the
type:

=IF(B36="","",24*60*(B36-$C$14)).

The formula above does the time subtraction and converts the
difference into (decimal) minutes. C14 is a reference time, formatted
as, er, time (hh:mm). The formula apparently works fine (e.g.
12:30-10:30 = 120) but on the graph all the data points are now
crowded together at the left hand side. If I hover the mouse pointer
above individual data points I can see why (sort of) ..

The second point (x-coord [in cell C37] is 150) indicates [Series
"Plasma curve" Point "150" (2,89.6)] where it should say [Series
"Plasma curve" Point "150" (150,89.6)]. Excel is seeing the "150"
time value OK (if I change the time of day in B37 the data label
changes), but plots it as "2", apparently because it is the second
point in the list! Similarly, the third data point (x coord = 180) on
the graph is labelled as [Series "Plasma curve" Point "180"
(3,72.7)] when it should be [Series "Plasma curve" Point "180"
(180,72.7)].

Any ideas?? Formatting problem? BTW, the x-axis of the graph is also
formatted as Number.
Click to expand...

I'd hazard a guess that your x-axis is formatted as a category type. I
won't try to replicate the details since Jon Peltier has the questions
answered on his page at
http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/CatVsValueAxis.html.

(I hope that fits. If not, copy and paste the URL to your browser.)

Dave
dvt at psu dot edu
 
Thanks Dave, but no. The x-axis is definately a Value axis (label confirms
this), as the graph was created as x-y scatter chart.
Problem must lie elsewhere.

As I mentioned, all I have really done is to change the content of the
Series X-values from simple numeric values to cells containing formulae. If
this provides a clue, the graph still worked OK when I had the following
formula in cell C36 (say): =24*60*IF(B36="","",B36-$C$14). This was really
a mistake (blank cell produced a #VALUE result instead of blank) but at
least the graph still worked! I then changed the formula to
=IF(B36="","",24*60*(B36-$C$14)) to cure the 'detect blank problem' but
suceeded in screwing up the graph! What is it about this change in formula
that confuses Excel?

Phil


dvt said:
Phil said:
Hi folks

I have a x-y scatter graph with a log scale y-axis. I recently had to
change the x source data, now the data does not plot correctly. To
give you an idea, the y-axis is percentage (3 cycles of log going up
to a maximum of 1000) and the x-axis is elapsed time in minutes going
from 0-240. I have 6 data points plotted. Before I made the recent
change the x-axis coordinates (elapsed time values) were just a
column of numbers (in Cells C36:C41), formatted as Number. Rather
than enter the (elapsed) time directly, I now want Excel to calculate
it (Time A - Time B) and use the result as the x-coordinate.

B36:B41 now has time-of-day input (12:30, 13:15, etc), formatted as
hh:mm, and C36:C41 (formatted as Number) now contains formulae of the
type:

=IF(B36="","",24*60*(B36-$C$14)).

The formula above does the time subtraction and converts the
difference into (decimal) minutes. C14 is a reference time, formatted
as, er, time (hh:mm). The formula apparently works fine (e.g.
12:30-10:30 = 120) but on the graph all the data points are now
crowded together at the left hand side. If I hover the mouse pointer
above individual data points I can see why (sort of) ..

The second point (x-coord [in cell C37] is 150) indicates [Series
"Plasma curve" Point "150" (2,89.6)] where it should say [Series
"Plasma curve" Point "150" (150,89.6)]. Excel is seeing the "150"
time value OK (if I change the time of day in B37 the data label
changes), but plots it as "2", apparently because it is the second
point in the list! Similarly, the third data point (x coord = 180) on
the graph is labelled as [Series "Plasma curve" Point "180"
(3,72.7)] when it should be [Series "Plasma curve" Point "180"
(180,72.7)].

Any ideas?? Formatting problem? BTW, the x-axis of the graph is also
formatted as Number.
Click to expand...

I'd hazard a guess that your x-axis is formatted as a category type. I
won't try to replicate the details since Jon Peltier has the questions
answered on his page at
http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/CatVsValueAxis.html.

(I hope that fits. If not, copy and paste the URL to your browser.)

Dave
dvt at psu dot edu
Click to expand...
 
Back
Top