time

  • Thread starter Thread starter milly
  • Start date Start date
M

milly

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.
 
Milly -

I assume the hour and minute values are whole numbers. By Julian Day you
mean a date like 2-Feb-04? Then it's easy. Insert a column after Minute.
Assuming this new column is D, Date is A, Hour is B and Minute is C,
labels are in row 1 and the data starts in row 2, use this formula in
cell D2:

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

Format this cell with a date-time number format of your choice, then
fill it down the column as far as you need.

In your prior charts, I wonder if you've been using the Line chart type.
This is inappropriate for plotting numerical X data. Use the XY Scatter
type instead. Despite the unfortunate choices of type names, you can
connect the points with lines if you desire. But you can also freely set
the endpoints of both axes in an XY Scatter chart.

You need to separate the different frequencies into different columns,
so they appear in separate series. How I'd do it is to list the
frequencies in the cells to the right of the Signal Strength header (in
G1 to whatever, in row 1). In G2 put this formula:

=IF($E2=G$1,$F2,na())

If the value in E2 matches the column heading, the value in F2 is placed
in the cell; otherwise the cell will contain #N/A, which looks ugly in
the sheet, but doesn't plot. But if you connect the points with a line,
the line will span the gap of #N/A values. This is like the Conditional
Chart example on my web site:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Fill this formula right and down as far as needed. Select the Date-Time
column you added above, then hold down Ctrl while selecting columns G to
whatever, then make your XY Scatter chart.

- Jon
 
In addition to Jon's suggestion, you might want to explore creating a
PivotTable (with a PivotChart) from your data. The Frequency would be
the most significant row field, the Day, Hour, and Minute would be the
others -- in descending order of significance. The Signal Strength
will be the data field. XL will default to 'Sum of Signal Strength' as
the data field, but that will work just fine in your case.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top