Combining separate day and time columns...

[nihilan]

Ok, I have a two part question here:

My data is set up with 5 columns:

Day (Julian) Hour Minute Frequency Signal Strength


First I want to find a way to collapse the Day, hour and minute into one column.

Second, I would like to plot the signal strength against the time for each of 20 frequencies (there are about 11,000 rows in this data set – each is a recording of one of 20 transmitters, the time it occurred and the strength of the signal). However, normally when you plot this sort of graph in excel the times with 0 values do not show up. I want an x axis that shows the entire time scale of interest (say the month of February) even if there are only 5 data points for that month.

Any help would be greatly appreciated.

 

[GaryB]

Welcome to the board!

if your date is in A2, your hour in B2 and your minutes in C2:-

=A2+(B2/24)+(C2/1440)

format this as something like "ddmmmyy hh:mm"

Hope this helps

GaryB

 

[nihilan]

Thanks for the help. So i've got the dates and times into one column. Now I essentially have 3 columns I want to work with, Date/time, frequency, and signal strenght. I have about 11,000 entries. I want to create a chart that shows time along the x axis, signal strength along the y and a different line for each of the 20 frequencies that I have.

Any ideas how to do this?


sample data:

Frequency Signal Strength Time/date
1012 80 2/18/00 12:19
1012 80 2/18/00 12:22
1012 87 2/18/00 15:31
1012 83 2/18/00 15:44
1012 82 2/18/00 15:57
1012 92 2/18/00 16:03
1012 85 2/18/00 16:15
1012 81 2/18/00 16:22
1012 84 2/18/00 16:31
1012 76 2/19/00 6:29
1012 78 2/19/00 6:33
1012 92 2/21/00 16:53
1012 107 2/22/00 14:33
1012 105 2/22/00 14:47
1012 103 2/22/00 14:53
1012 94 2/22/00 15:02
1012 104 2/22/00 15:10
1023 84 2/13/00 20:30
1023 84 2/13/00 20:43
1023 87 2/13/00 20:59
1023 85 2/13/00 21:08
1023 84 2/13/00 21:10
1023 85 2/13/00 21:23
1023 81 2/13/00 21:30
1023 82 2/13/00 21:48
1023 81 2/13/00 21:50
1023 81 2/13/00 22:01
1023 82 2/13/00 22:17
1023 89 2/13/00 22:26
1023 84 2/13/00 22:30
1023 83 2/13/00 22:46
1023 82 2/13/00 22:51
1023 86 2/13/00 23:08
1023 84 2/13/00 23:11
1023 80 2/13/00 23:26
1023 79 2/13/00 23:31


Thanks again for any help.

 

[GaryB]

I'm not too hot on graphs, but I think you're going to need to produce a pivot table from your data and produce a pivot chart from that.

HTH
GaryB