Too many data points

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Cowtoon" <[email protected]> wrote in message
news:[email protected]...
>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

Cowtoon wrote:
> 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.

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

"Bernard Liengme" wrote:

> 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
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Cowtoon" <[email protected]> wrote in message
> news:[email protected]...
> >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 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

In article <[email protected]>,
[email protected] says...
> 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
>
> "Bernard Liengme" wrote:
>
> > 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
> > --
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "Cowtoon" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> >
> >
> >
>

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" wrote:

> 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
>
> In article <[email protected]>,
> [email protected] says...
> > 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
> >
> > "Bernard Liengme" wrote:
> >
> > > 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
> > > --
> > > Bernard V Liengme
> > > www.stfx.ca/people/bliengme
> > > remove caps from email
> > >
> > > "Cowtoon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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
> > >
> > >
> > >
> >
>

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

In article <[email protected]>,
[email protected] says...
> 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" wrote:
>
> > 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
> >
> > In article <[email protected]>,
> > [email protected] says...
> > > 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
> > >
> > > "Bernard Liengme" wrote:
> > >
> > > > 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
> > > > --
> > > > Bernard V Liengme
> > > > www.stfx.ca/people/bliengme
> > > > remove caps from email
> > > >
> > > > "Cowtoon" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >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
> > > >
> > > >
> > > >
> > >
> >
>

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

"Tushar Mehta" wrote:

> 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
>
> In article <[email protected]>,
> [email protected] says...
> > 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
> >
> > "Bernard Liengme" wrote:
> >
> > > 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
> > > --
> > > Bernard V Liengme
> > > www.stfx.ca/people/bliengme
> > > remove caps from email
> > >
> > > "Cowtoon" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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
> > >
> > >
> > >
> >
>

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

"coj" wrote:

> Cowtoon wrote:
> > 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.
>

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

In article <[email protected]>,
[email protected] says...
> 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}