Too many data points

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data that's collected every two minutes, 24 hours a day and only want
to chart the data for every hour (on the hour). How do I extract that
information, so that my generated chart doesn't look a huge blob of ink.
There must be a formula that can extra the info (say ... if the minutes =
00), that places it in another worksheet where I can generate a
less-cluttered chart from those data.

Thanks for any assistance.
Diana
 
Let's say x-values in A2:A2000, y-values in B2:B2000 with headers in row 1
To plot every 20th row:
In C2 enter =IF(mod(ROW(),20)=0,B2,NA())
Copy down to C2000
Select A1:A2000; hold CTRL; select C1:C2000; make chart
The N/A data is ignored
 
Cowtoon said:
I have data that's collected every two minutes, 24 hours a day and only want
to chart the data for every hour (on the hour). How do I extract that
information, so that my generated chart doesn't look a huge blob of ink.
There must be a formula that can extra the info (say ... if the minutes =
00), that places it in another worksheet where I can generate a
less-cluttered chart from those data.

Thanks for any assistance.
Diana
Click to expand...

Why not leave the data where it is and use a Data Filter Autofilter to
show only the data you want to see ? Excel graphs only show visible rows
(and columns) of data.

You can use Data Filter Showall to get back all the data as visible
again when you need.
 
Bernard ... you've taken me closer - thanks for that. I wonder if I can
explain what's happening now.
I just realized a problem. My x values are in say A5:A2000 and then my y
values are in B5:B2000 as well C5:B2000 (temperature and rel. humidity from a
data collector). It's not a scientific experiment ... just readings for room
temps and humidity.

So ... instead of a relationship between rows and column values, all of the
data is in columns. Is there a way to rotate, say the time data to appear in
a row. There's more than 256 readings ... not sure if Excel can take it.
I'd appreciate your thoughts.
Diana
 
Why rotate anything? Bernard's solution should work just fine. Enter
it in D5 rather than B2.

So, if A contains the time, in D5, enter =IF(MINUTE(A5)=0,A5,NA())
In E5 enter the formula =IF(ISNA($D5),NA(),B5).

Copy E5 to F5. Copy D5:F5 as far down as you have data. Plot D5:F
{whatever}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar, I must be doing something very wrong then.
His formula works for the data points ... rather perfectly. I haven't tried
your suggestion yet, but I'll mention here, that the chart isn't picking up
the (date/time) in the A column, even though I've selected it for generating
the chart.

The x axis (across the bottom) has whole numbers that closely match the row
numbers (but not exactly). It makes no sense to me. Meanwhile, I'll take a
look at what you wrote. Thanks for the response. Appreciated.
Diana
 
To Tushar and Bernard:
I got it to work!
Tushar I kind of took what you said and modified it a bit.
I moved my formulas that Bernard gave me and put them to the immediate right
of the date/time info. I then modified the cell reference and filled
downwards. I regenerated the chart and voila! ... it's exactly what I want.
Perhaps separating the from the time/date info was causing the relationship
to be lost ... but I'm not sure.
Thank you both so much.
Diana
 
Coj. I was able to solve the problem before I got a chance to try your
solution, but thanks anyway.
Diana
 
Hi Diana,

Glad you got that sorted out. And, thanks for letting folks know.

--
Regards,

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

To Tushar and Bernard:
I got it to work!
Tushar I kind of took what you said and modified it a bit.
I moved my formulas that Bernard gave me and put them to the immediate right
of the date/time info. I then modified the cell reference and filled
downwards. I regenerated the chart and voila! ... it's exactly what I want.
Perhaps separating the from the time/date info was causing the relationship
to be lost ... but I'm not sure.
Thank you both so much.
Diana
Click to expand...
{snip}
 
Back
Top