Time graphs

Hi,

How do I plot data along a time value (x axis) if the time is
continuous?  In other words, if a data value is "3" for five seconds
then a line will be drawn on the graph and then when the value changes
to "2" for two seconds, the line steps down to the "2" value and plots
for two seconds until the next value change?  I basically want a graph
which has time along the bottom (x axis) and a value of 1, 2 or 3 on
the left (y axis), so as the value changes the line is plotted along
the time axis.

Hope I've explained myself?
On intend to use a macro to capture the time value of each value
change to create a table which the graph will use.

regards,
Matt
0
4/15/2009 8:20:19 AM
excel 39879 articles. 2 followers. Follow

8 Replies
701 Views

Similar Articles

[PageSpeed] 34

Excel 2007
Step plot climbs in real time:
http://www.mediafire.com/file/irwmlz1jcnn/04_15_09b.xlsm
0
4/15/2009 10:40:40 PM
On Apr 15, 6:20=A0pm, guerilla <matt.j.ke...@gmail.com> wrote:
> Hi,
>
> How do I plot data along a time value (x axis) if the time is
> continuous? =A0In other words, if a data value is "3" for five seconds
> then a line will be drawn on the graph and then when the value changes
> to "2" for two seconds, the line steps down to the "2" value and plots
> for two seconds until the next value change? =A0I basically want a graph
> which has time along the bottom (x axis) and a value of 1, 2 or 3 on
> the left (y axis), so as the value changes the line is plotted along
> the time axis.
>
> Hope I've explained myself?
> On intend to use a macro to capture the time value of each value
> change to create a table which the graph will use.
>
> regards,
> Matt

Hi Matt,

If you data looks like...

(0, 1)
(3, 2)
(7, 3)
(12, 2)
(15, 1)
(22, 3)

where the left value is "time when change occurred" and the right
value is "the new value" out of just 1,2 and 3, then you need to add
points to the data that will result in square steps rather than the
angled steps that the above data produces when used to make an XY
(Scatter) chart.

If the time values start in A2 and the new values in B2 then...

=3DINDIRECT("A" &INT(ROW(1:1)/2)+2)

and

=3DINDIRECT("B"&INT(ROW(2:2)/2)+1)

both filled down far enough (11 rows for above example) will add the
data points needed to produce square steps...

(0, 1)<original
(3, 1)<added
(3, 2)<original
(7, 2)<added
(7, 3)<original
(12, 3)<added
(12, 2)<original
(15, 2)<added
(15, 1)<original
(22, 1)<added
(22, 3)<original

Note that the added data points plot the old value at the same time
that the value changed to the new value.

Ken Johnson

0
KenCJohnson (314)
4/15/2009 10:52:00 PM
To create the step chart directly you can try this...

eg Data range: Sht!A2:B7

0 1
3 2
7 3
12 2
15 1
22 3

First create a line or area chart with a time value x-axis.

Then modify the series formula to:
=SERIES(,(Sht!A2:A7,Sht!A2:A7),(Sht!B2,Sht!B2:B6,Sht!B2:B7),1)

[This duplicates the data with the y-values offset by one,
in this way no additional values need to be added.]

"guerilla" wrote:

> Hi,
> 
> How do I plot data along a time value (x axis) if the time is
> continuous?  In other words, if a data value is "3" for five seconds
> then a line will be drawn on the graph and then when the value changes
> to "2" for two seconds, the line steps down to the "2" value and plots
> for two seconds until the next value change?  I basically want a graph
> which has time along the bottom (x axis) and a value of 1, 2 or 3 on
> the left (y axis), so as the value changes the line is plotted along
> the time axis.
> 
> Hope I've explained myself?
> On intend to use a macro to capture the time value of each value
> change to create a table which the graph will use.
> 
> regards,
> Matt
> 
0
lori1 (126)
4/17/2009 12:01:02 AM
On Apr 17, 10:01=A0am, Lori <L...@discussions.microsoft.com> wrote:
> To create the step chart directly you can try this...
>
> eg Data range: Sht!A2:B7
>
> 0 1
> 3 2
> 7 3
> 12 2
> 15 1
> 22 3
>
> First create a line or area chart with a time value x-axis.
>
> Then modify the series formula to:
> =3DSERIES(,(Sht!A2:A7,Sht!A2:A7),(Sht!B2,Sht!B2:B6,Sht!B2:B7),1)
>
> [This duplicates the data with the y-values offset by one,
> in this way no additional values need to be added.]
>
> "guerilla" wrote:
> > Hi,
>
> > How do I plot data along a time value (x axis) if the time is
> > continuous? =A0In other words, if a data value is "3" for five seconds
> > then a line will be drawn on the graph and then when the value changes
> > to "2" for two seconds, the line steps down to the "2" value and plots
> > for two seconds until the next value change? =A0I basically want a grap=
h
> > which has time along the bottom (x axis) and a value of 1, 2 or 3 on
> > the left (y axis), so as the value changes the line is plotted along
> > the time axis.
>
> > Hope I've explained myself?
> > On intend to use a macro to capture the time value of each value
> > change to create a table which the graph will use.
>
> > regards,
> > Matt

Hi Lori,

I tried that out with an XY (Scatter) chart with data in A2:B7 on
Sheet1 using...

=3DSERIES(,(Sheet1!$A$2:$A$7,Sheet1!$A$2:$A$7),(Sheet1!$B$2,Sheet1!$B
$2:$B$6,Sheet1!$B$2:$B$7),1)

It results in the correct set of points, however the lines joining
them are all over the place.

Could I be doing something wrong?

Ken Johnson
0
KenCJohnson (314)
4/17/2009 1:34:51 AM
Ken - You need to use this technique with line or area charts only. 
Make sure to set the category axis to time scale, you can then 
reformat the x-axis eg to display as general number format.
XY charts connect points consecutively, so this method is not appropriate.

For an XY chart you could use instead: =SERIES(,Sht!x,Sht!y,1) 
where you define then names to be...

x: =$A$2:$A$7+{0,0}
y: =LOOKUP(ROW($A$2:$A$7)-{1,0},ROW($A$2:$A$7),$B$2:$B$7)

[The sheet name will be added to the name automatically] 
This also seems to work with line and area charts too.

HTH, Lori
0
lori1 (126)
4/17/2009 5:13:01 AM
Excel 2007
More stuff on step graph, scatter plots
and range unions.
http://www.mediafire.com/file/go3d4znvj5k/04_17_09.xlsx
0
4/17/2009 11:04:53 PM
> For an XY chart you could use instead: =SERIES(,Sht!x,Sht!y,1)
> where you define the names to be...
>
> x: =$A$2:$A$7+{0,0}
> y: =LOOKUP(ROW($A$2:$A$7)-{1,0},ROW($A$2:$A$7),$B$2:$B$7)
>
> This also seems to work with line and area charts too.
>
> HTH, Lori

This didn't come through in google groups for some reason.
In addtion, if the names refer to a table range, the chart will auto-update. 


0
LoriMiller (18)
4/20/2009 5:13:19 AM
On Apr 20, 3:13=A0pm, "Lori Miller"
<lorimil...@discussions.microsoft.com> wrote:
> > For an XY chart you could use instead: =3DSERIES(,Sht!x,Sht!y,1)
> > where you define the names to be...
>
> > x: =3D$A$2:$A$7+{0,0}
> > y: =3DLOOKUP(ROW($A$2:$A$7)-{1,0},ROW($A$2:$A$7),$B$2:$B$7)
>
> > This also seems to work with line and area charts too.
>
> > HTH, Lori
>
> This didn't come through in google groups for some reason.
> In addtion, if the names refer to a table range, the chart will auto-upda=
te.

Thanks Lori.
Very interesting technique.

Ken Johnson
0
KenCJohnson (314)
4/20/2009 10:56:49 AM
Reply:

Similar Artilces:

Time zone
When I noticed the clock on my pc was wrong, I changed the time zone from Pacific to Eastern which is my correct U.S. time zone. After doing this, all of the items inserted as "all day" appointments in my monthly calendar are displayed with an image of a clock on either side of the appointment. The option to display clocks is turned off in the settings. Why is this happening and how can I prevent it from happening. Right now, I've had to put back the Pacific time zone and adjust to the pc not telling the right time. Is there any other solution? Thanks! ddlaz No ...

Send/Receive continues 1000s of times
When Outlook does its regular send/receive it ends up sending and receiving thousands of times. The task pane shows an ever increasing number of tasks that it says have been completed successfully. When all it actually is doing is rechecking the mail accounts I have, over and over again. Literally thousands of times. This interferes with anything that I want to do in Outlook while this is going on. It seems to have started after I downloaded either the latest version of itunes or the latest version of Adobe (or some other application like that). Help! And thanks. Per Roady - MVP, th...

One time code
In either an SDI or MDI application, if I want to include some functionality ( such as serial port configuration and control thread) that can only be run once per application instance, where should that code go? I looked at the App class but it doesn't handle user messages ( at least by default ). Should the main code and worker thread go in the MainFrm class in the OnCreate function? Help? Ron H. ----------------- www.Newsgroup-Binaries.com - *Completion*Retention*Speed* Access your favorite newsgroups from home or on the road ----------------- If it has to do with the "wi...

cpu time
Hi, Can you tell me about good tool for vs-6 that can tell me what functions in my application take the most cpu time? Thanks! In article <u$YZa3o9EHA.2680@TK2MSFTNGP09.phx.gbl>, dbg@012.net.il says... > Hi, > Can you tell me about good tool for vs-6 that can tell me what functions in > my application take the most cpu time? VS 6 includes a perfectly good profiler, which does exactly this. If you're working inside the environment, go to the link tab in the project settings, and click the "enable profiling" check box, then do a 'rebuild all' to gene...

Graph troubles
Hello, I am trying to make a graph that would show the sales for the US and overlapping would be sales by area. I have a presentation to do for the area managers and would like to show the how they relate to the entire US. I am using excel 2000 and my data looks like this: July......Jan 04 Projected Total US (A2)July Area A(A3) Area B (A4) Can anyone help me? Thanks, Chris Chris Areas Sales % Sales Area 1 23456 0.3 Area 2 45321 0.6 Area 3 3243 0 Total 72020 Try a Line, Column on 2 Axis chart and calculate the percentage of each area sales of the total. Regards ...

Time Out a MsgBox
I have a file that runs a macro when it is open. This file may be opened manually or automatically by a macro from another file. If the file is opened manually I would like a MsgBox to prompt the user to end the macro before it gets into the body of the code. If the file is opened automatically I would like the MsgBox to automatically respond after a time delay to allow the macro to continue running the bulk of the code. Is there anyway to force a response to a MsgBox after a preset amount of time with no response from the user? Hi, How about this instead which closes...

Calculating time and pay in excel
Hi, i'm trying to make a spreadsheet that can calculate pay per minute depending on an average length of time worked. Its driving me crazy and any help would be greatly appreciated! Coloumn A is length of time, at the bottom is a total time, next to that is an average length of time worked and this is then multiplied by the amount appropriate for that average. What would be the best way to work all of this out accurately? Many thanks for any help you can give Mark -- wobbleman ------------------------------------------------------------------------ wobbleman's Profile: http://www.e...

Start Time & End Time
I have a table of data for my Scheduled cutting list that is exported from another program into an Excel Spreadsheet and from this table I would like to be able to find the start and end time of the production runs automatically in a separate table. The table is set up like this Planned order Time Product Description Volume 12345 08:00:00 Product 1 3500 23435 11:32:00 Product 2 5680 14567 13:24:00 Product 3 10000 and so on. I can find the start time by us...

Am I behind the times?
I have just installed Exchange sp2 on my SBS 2008 boxes and all went well. One weird thing at the end of one of the installs it said Web Apps (I think) couldn't restore default configuration, but everything seems to be working just fine. So then I went to look for updates and I see a few that I must have missed. I use WSUS on the network. One is Exchange SP2 update rollup 3. Go for it or no? Second, Windows Server 2008 SP2. Yikes, when did that come out for SBS? I wonder why it never popped up before since it looks like it's been out for quite some time. Nonetheless, s...

run time error
When I attempt to import a text file (.txt) in to Access, an error occurs. Run-time error �3163�: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data When I click the Debug button this pops up: .Fields("TranType").Value = rs("MoveType").Value A different error occurs when save it to my desktop, and this is what happens.Run-time error �2185�: You can�t reference a property or method for a control unless the control has the focus. When I click the Debug button this pops up: MsgBox "Import File Not Found:&quo...

Incorrect Time Stamps
Exchange seems to be stamping the wrong time on emails and events sporadically. All the times on servers are correct. Any thoughts? -- Craig Tuttle - Level I Exchange Tech UCNSB.org The time stamp can also be handled by the client. Which exact time field is being stamped wrong? Have you verified that the client has the correct time (i.e. Daylight savings time set correctly)? -- Ben Winzenz Exchange MVP MessageOne "Craig Tuttle" <CraigTuttle@discussions.microsoft.com> wrote in message news:02D51926-77DC-43EB-A5B5-7A00F4530323@microsoft.com... > Exchange seems to b...

LIne Graph #5
How do I insert a dot on my line graph which indicates the value it represents Right click on the line, choose Format, and apply a marker to the series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "malo" <malo@discussions.microsoft.com> wrote in message news:7DFD90F9-36DD-4931-9D21-2DC0080768EA@microsoft.com... > How do I insert a dot on my line graph which indicates the value it > represents ...

Transferring freight more than one time on a sales order
We frequently have partial shipments of sales orders and we leave the sales order open until everything has shipped instead of creating backorders. The system will allow you to have several tracking numbers for each order but you can only transfer the freight once. I would like to make the suggestion to be able to transfer freight more than one time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

How chart time scale x-axis with another date serie
Hello all. I have this problem, I will need to create this chart that I don't know how to. Serie 1 is below which needs to be time scaled Date Count 1/1/2006 12 1/2/2006 3 1/3/2006 5 2/1/2006 18 2/3/2006 4 2/18/2006 9 4/1/2006 18 7/4/2006 38 Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis with symbols. 1/31/2006 2/18/2006 4/2/2006 I will look something like you have a line of count on a time-scaled chart, with 3 dates point in the x-axis. Please ...

Activity default due time
I need to stop the default coming up as 12:00am and set it to something like 9am so people get reminders at reasonable times. How can this be done? thanks Adrian Adrian, If you don't mind writing a few lines of code, look at "Setting a default time in a date field" in Client Side Scripting - More JavaScript Code - Part 3 (http://www.stunnware.com/crm2/topic.aspx?id=JS12). -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup The ...

Time / Negative Time
Hi, If, for instance, in Excel you write 17:00 - 16:30, it will produc 0:30. However, 16:30 - 17:00 will produce #####, understandibly because you cannot have - ? o'clock. By formatting this to a number can produce -0.5. What if I still want to represent the decimal, but out of 60, so that can still get -0.30. I have tried messing about with fractions and still can't get it. Any ideas? Thanks, -- Message posted from http://www.ExcelForum.com On Thu, 13 May 2004 17:33:58 -0500, Kiaat <<Kiaat.167uek@excelforum-nospam.com>> wrote: >Hi, > >If, for insta...

Need to get slope of peaks on excel graph
I have a graph with many peaks which are decreasing logarithmically and i need to get the slope of these peaks ignoring the "Noise" which is below these peaks. Any suggestions I'm sure your question makes sense to you but by definition a peak has a slope of zero. Or do you mean the slope of a curve that includes only the peaks? If so, plot the peak values as a 2nd series. If that is a straight line, the trendline (or the LINEST function) will give you the slope. -- Regards, Tushar Mehta www.tushar-mehta.com Multi-disciplinary business expertise + Technology skills...

How to Calulculate Hrs and Mins between time frames
This is a multi-part message in MIME format. ------=_NextPart_000_0293_01C68406.3D280160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable If i have 2 cells with time values say: A1=3D7:30AM and B1=3D3:30PM. 7:30AM - 3:30PM set to NORMAL Hours 3:30PM - 6:30PM set to TIME & HALF Hours Any time after 6:30PM - 7:29AM to be DOUBLE TIME hours Then if i set 4 other cells for data input: A2=3D( DATE 1) 29/5/2009 Start date. B2=3D(Date 2) 29/5/2006 = Finish date. A3=3D(Time) 7:30AM Start Time. B2=3D (Time) 7:00PM =...

Time segments in OWA
I need to change the time segments in OWA when creating new appointments. The default is 30 minutes but I need 20 minutes. I can't seem to find the right code in the exchsrvr exchweb controls. I'm close but can't find the time array expression tha generates the 30 minute increments. Can anybody help me with this? Thanks, Brian Techhead <jorgenson.b@gmail.com> wrote: > I need to change the time segments in OWA when creating new > appointments. The default is 30 minutes but I need 20 minutes. I can't > seem to find the right code in the exchsrvr exchweb c...

calculate time and date
I want to find out how many houer iy is between one date/time and another date/time. date and time one cell A1= 04.03.2005 08:30 date/time two cell A2= 05.03.2005 23:59 =A2-A1 custom format [hh:mm] or for Norway [tt:mm] -- Regards, Peo Sjoblom "make" <lage@tiscali.no> wrote in message news:2c61523.0503070721.2dacdd07@posting.google.com... > I want to find out how many houer iy is between one date/time and > another date/time. date and time one cell A1= 04.03.2005 08:30 > date/time two cell A2= 05.03.2005 23...

How Can I Know last time was activity on a database
I'm doing a process that uses databases from sys.databases. I would like to know when is the last time happened some activity there. (insert, update) I've tryed to look at sys.tables but only shows me last structural change on it. Not last time data has changed. How this can be done? Carles Oriol SQL Server doesn't store this information (imagine the overhead for all those who doesn't need it). You can for instance use a trigger to keep track of that information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sq...

Search for documents by time modified
I need to narrow a search for documents by author, a range of time (say from 5 p.m. to 8 p.m.), and a range of years (say from 1998 to 2004). They would need to include Word Perfect and Word documents. Thanks for any help. Version of Windows? Use Advanced Search. But if you're using Word2007 in Windows XP, you might not have much of a search function (it pretty much got moved from Office to Windows with the new versions). On Feb 19, 9:01=A0pm, D. Ring <D. Ring @discussions.microsoft.com> wrote: > I need to narrow a search for documents by author, a range ...

How do I set up secondary axis on bar graph. What do I highlight?
I am having trouble adding a secondary axis to a bar graph. Can you help? What do I highlight? please walk me through this .thanks Hi, This explains how to display the secondary axes. http://www.andypope.info/tips/tip008.htm Cheers Andy inquiringmind73 wrote: > I am having trouble adding a secondary axis to a bar graph. Can you help? > What do I highlight? please walk me through this .thanks -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Andy - You should note at the top of your page (tip008.htm) that you need a second series before you can have a secondary axis...

Time, time, time, always an issue
Here’s my problem: I have a date/time field recording the date/time when pt arrives at our hospital. My final output needs to include 2 new fields: - day of week pt arrived: Sun, Mon, Tues, etc (this I can do) - time pt arrived, renamed as: Regular (6am – 5:59 pm) and Evening (6pm - 5: 59 am) This one I have no idea!! Any Suggestions? Thanks. Phredd wrote: >I have a date/time field recording the date/time when pt arrives at our >hospital. >My final output needs to include 2 new fields: >- day of week pt arrived: Sun, Mon, Tues, etc (this I can do) >- time pt arrived, re...

creating a graph from a drop down menu
Hi, I have to create a database that will be used to analyse some questionnaire and have made a spreadsheet where the answers to the questions (which within the questionnaire are on a strongly agree - strongly disagree rating scale) are in a drop down menu. I was wondering if I would be able to make a graph from this data? Cheers Well, the short answer is yes, but how this is accomplished depends on how your data is structured. Essentially, you will need 5 or 7 cells which will contain the number of responses in the categories from Strongly agree to strongly Sisagree for the que...