Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Chart help - cannot display all data
Excel 2000
At various times during each day I have to measure my blood pressure. Each time I take 4 readings (simplified version). A: DATE B: TIME C: BP A1: 01/01/2007 B1: 09:00 C1: 75 A2: 01/01/2007 B2: 09:01 C2: 77 A3: 01/01/2007 B3: 09:02 C3: 74 A4: 01/01/2007 B4: 09:03 C4: 77 I can generate a scatter chart with Days on the x axis, BP on the y axis and the C data as vertical columns of short horizontal lines ( - ) for each day. This method produces a good graphical representation of the range of the day's readings, but multiple readings with the same value are only displayed as a single point on the chart. i.e. 1 off 74, 1 off 75 and 1 off 77 I still need to create a chart that will display every reading so I tried using a helper column D with the values A+B i.e. D1: 39083.375 D2: 39083.37569 D3: 39083.37639 D4: 39083.37708 I then used these values for the x axis and series range but this only worked over a minute spread of D values before, once again, failing to display multiple values correctly. Quite probably a "senior moment", but any help would be greatly appreciated. -- Fred |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Chart help - cannot display all data
Could you use date&time as the x-values?
With your present data, insert a new C column In C1 enter =A1+B1 and format with Custom Format mm/dd/yy hh:mm (or dd/mm/yy hh:mm if Canadian or British) Now make an XY chart with columns B and C. It will look better with more than 4 data points. best wishes from (Old)Bernard -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Fred" wrote in message ... Excel 2000 At various times during each day I have to measure my blood pressure. Each time I take 4 readings (simplified version). A: DATE B: TIME C: BP A1: 01/01/2007 B1: 09:00 C1: 75 A2: 01/01/2007 B2: 09:01 C2: 77 A3: 01/01/2007 B3: 09:02 C3: 74 A4: 01/01/2007 B4: 09:03 C4: 77 I can generate a scatter chart with Days on the x axis, BP on the y axis and the C data as vertical columns of short horizontal lines ( - ) for each day. This method produces a good graphical representation of the range of the day's readings, but multiple readings with the same value are only displayed as a single point on the chart. i.e. 1 off 74, 1 off 75 and 1 off 77 I still need to create a chart that will display every reading so I tried using a helper column D with the values A+B i.e. D1: 39083.375 D2: 39083.37569 D3: 39083.37639 D4: 39083.37708 I then used these values for the x axis and series range but this only worked over a minute spread of D values before, once again, failing to display multiple values correctly. Quite probably a "senior moment", but any help would be greatly appreciated. -- Fred |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Chart help - cannot display all data
I then used these values for the x axis and series range but this only
worked over a minute spread of D values before, once again, failing to display multiple values correctly. If you have several values each day, but they are measured between 9:00 and 9:03, then when multiple days are included, the spread between 4 measurements in 3 minutes will be overwhelmed by the intervening 23 hours and 57 minutes. Follow Bernard's suggestion (i.e., format your column D in date + time format, and insert it before the BP measurements), then make your chart; but where Bernard says XY chart, instead make a Line chart. Then go to Chart menu Chart Options Axes tab, and under Category (X) Axis, select Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Fred" wrote in message ... Excel 2000 At various times during each day I have to measure my blood pressure. Each time I take 4 readings (simplified version). A: DATE B: TIME C: BP A1: 01/01/2007 B1: 09:00 C1: 75 A2: 01/01/2007 B2: 09:01 C2: 77 A3: 01/01/2007 B3: 09:02 C3: 74 A4: 01/01/2007 B4: 09:03 C4: 77 I can generate a scatter chart with Days on the x axis, BP on the y axis and the C data as vertical columns of short horizontal lines ( - ) for each day. This method produces a good graphical representation of the range of the day's readings, but multiple readings with the same value are only displayed as a single point on the chart. i.e. 1 off 74, 1 off 75 and 1 off 77 I still need to create a chart that will display every reading so I tried using a helper column D with the values A+B i.e. D1: 39083.375 D2: 39083.37569 D3: 39083.37639 D4: 39083.37708 I then used these values for the x axis and series range but this only worked over a minute spread of D values before, once again, failing to display multiple values correctly. Quite probably a "senior moment", but any help would be greatly appreciated. -- Fred |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Chart help - cannot display all data
"Bernard Liengme" wrote in message
... Could you use date&time as the x-values? With your present data, insert a new C column In C1 enter =A1+B1 and format with Custom Format mm/dd/yy hh:mm (or dd/mm/yy hh:mm if Canadian or British) Now make an XY chart with columns B and C. It will look better with more than 4 data points. best wishes from (Old)Bernard -- Bernard V Liengme www.stfx.ca/people/bliengme Thanks for your prompt reply Bernard. Your solution was effectively the same as the "simplified??" description in my original post and thankfully confirmed that I was on the right track! Armed with your confirmation I looked even more closely at my data and realised, for the first time, that some of the time intervals (timestamped automatically by my BP monitor) were well under a minute apart. This explained why some of my chart points were a couple of pixels wider than the norm as the points were being correctly plotted but on top of each other! Manually editing the times to adjacent minute intervals solved my problem. Thanks again for your help. Regards, -- Fred |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Chart help - cannot display all data
"Jon Peltier" wrote in message
... If you have several values each day, but they are measured between 9:00 and 9:03, then when multiple days are included, the spread between 4 measurements in 3 minutes will be overwhelmed by the intervening 23 hours and 57 minutes. Follow Bernard's suggestion (i.e., format your column D in date + time format, and insert it before the BP measurements), then make your chart; but where Bernard says XY chart, instead make a Line chart. Then go to Chart menu Chart Options Axes tab, and under Category (X) Axis, select Category. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com Thank you Jon for your prompt reply. As per my reply to Bernard you will see that I have now solved this problem. You are, of course, completely correct in saying that such small time increments will be swamped. That was one of the reasons for my post - the vain hope that one of you gurus had managed the impossible and found a way of getting all this data onto a sheet of A4 paper! I have resorted to using the Scatter chart in favour of the Line chart because, over the last year, my GP has always visualised a trend line through the middle of my Line chart and totally ignored the fact that the upper and lower data limits are way outside spec! Now, when 6 of the 16 daily points are clearly above her "line", she cannot possibly deny the tablets aren't working. Surprisingly, I have found that a year's Scatter chart with this density of data displays remarkably well when using 5pts sized data markers. I find that it is more easily understood by a lay person (I am not referring to my GP) because they can read off the actual numbers instead of looking at a meaningless (to them) line zooming up and down across the page. It is also easier for me to demonstrate an exact value at any point on the chart. Thanks again for your expert help on these groups. Regards, -- Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Graph - Display Y axis data at top of chart(?) | Excel Discussion (Misc queries) | |||
How do I display the sum of 2 data series on a column chart? | Charts and Charting in Excel | |||
Display a chart only if there is data | Excel Discussion (Misc queries) | |||
Can I display the actual data in the data field of a pivot chart? | Excel Discussion (Misc queries) | |||
How do I display a data table in an Excel 2002 chart? | Charts and Charting in Excel |