Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#6
|
|||
|
|||
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 , 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" wrote in message ... 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 |
#7
|
|||
|
|||
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 , 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 , 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" wrote in message ... 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 |
#8
|
|||
|
|||
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 , 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" wrote in message ... 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 |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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 , 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} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
space between y axis and data points.. | Charts and Charting in Excel | |||
Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) |