Too many data points

[Cowtoon]

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

 

[Bernard Liengme]

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

 

[coj]

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

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.

 

[Cowtoon]

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

 

[Tushar Mehta]

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

 

[Cowtoon]

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

 

[Tushar Mehta]

Diana, you may also want to look at
Select Markers
http://www.tushar-
mehta.com/excel/newsgroups/only_some_markers/index.html

--
Regards,

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

 

[Cowtoon]

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

 

[Cowtoon]

Coj. I was able to solve the problem before I got a chance to try your
solution, but thanks anyway.
Diana

 

[Tushar Mehta]

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

{snip}