Another easy Excel question?

  graham√ 09:27 18 Jun 03
Locked

I have a photocell which switches on a floodlight on a tree in my garden, and have now started an Excel worksheet to track the time. To start, I have the date, 17 jun 03, in column A, the time as 9.20 in column B. I would like to have a graph of the data but the penny hasn't dropped yet, I suspect it's the time format. I have put in some dummy data for now.

A trivial subject maybe, but a nice graph! Anyone with Excel on their PC who have never used it, give it a try. I'm hooked!

  VoG® 09:46 18 Jun 03

Well, you will need some data to plot. You could use Column C and enter 0 when it switches off and 1 when it switches on.

I also think that you will need to combine Columns A and B to show Date+Time.

  graham√ 10:02 18 Jun 03

Thanks, wouldn't a different time on subsequent days be sufficient data? If the date was on the bottom (is that 'x' axis?) and the time was on the left (y?), then I would expect to see a change until 21st June, which is the longest day, then the trend should reverse as the days grow shorter.

  VoG® 10:14 18 Jun 03

OK I think I've cottoned-on now.

Select the data in Column B and click the ChartWizard toolbar button.

Select chart type (presumably XY (scatter)).

Click the Next button.

Select the Series tab.

Click in the X Values box and click the funny red arrow thingy at the right hand side of the box (sorry if this is getting technical).

Select your dates in Column A.

Click the funny red arrow thingy again.

Follow the rest of the Wizard to complete the chart.

Voila!

  seedie 10:28 18 Jun 03

for years I've been typing in the date in excel, then a few days ago I dicovered this.

CTRL+; puts in the current date
CTRL+: puts in the current time
You can combine them in one cell

CD

  graham√ 10:43 18 Jun 03

Thanks from me, and all the people following this lesson!

VoG®, had to faff about with the date, it just numbered 0 - 10 at first, just what I wanted now. Only thing is, how do I 'refresh' the graph as I input the new time each day, add days on and extend the time range?

  VoG® 10:51 18 Jun 03

To avoid faffing around the date, select Column A, Format/Cells and format as Date.

To get it to update you have two options:

a) Decide in advance how long this is going to run for, enter all of those dates and select the whole lot for your chart. To prevent it from plotting zeroes for missing times, enter

=NA()

in the time values for future dates.

b) Select the "new" data (both Columns A and B). Select the chart, Edit/Paste special and select:

Add cells as new points

Values Y in columns

Categories (X values) in first column.

  Peverelli 11:03 18 Jun 03

Ahhh, so that's how I get a chart to look the way I want it!!! Cheers VoG® & thanks graham√ for posing the question, and also to Seedie for the handy little tip.

  VoG® 11:05 18 Jun 03

Just spotted an error:

b) Select the "new" data (both Columns A and B) and Copy. Select the chart, Edit/Paste special and select:

  graham√ 11:08 18 Jun 03

Thankyou, perfect. Until the next time...

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…