Charting the last 60 entries in a table

[Gordon K]

As I add new data to my table I want to chart only the last 60 (Preferably a user defined number) data sets. How do I accomplish this?

I edited some data manually before inserting my chart. I discovered that my chart depended on that edit. If you delete any time and date data in any other row other than row 19 the chart changes as expected. If you delete A19 something weird happens. Cell A19 contains the edited data. Can anyone explain what is going on? Placing a single quote before any value in column A returns the chart to the expected appearance.

This is my first attempt to use Dropbox. I hope you can download my macro enabled spreadsheet. I saw an error message but could open and download the file.

https://www.dropbox.com/s/m1twrz5jaelje5o/test_blood_pressure_pulse.xlsm?dl=0

My goal was to create a spreadsheet that would allow entry of blood pressure as quickly as humanly possible using only the numeric keypad. I use the system time and date when I at my computer and enter them manually when I am not.

This is a partial screen shot of my worksheet.

https://www.dropbox.com/s/9vdq672d67qmgoz/BP data and chart.JPG?dl=0





Excel 2007
Windows 10

 

[sheetspread]

A19 is formatted as text, perhaps the result of a download. If you double click on the cell and press enter it will "recalculate" to a number. Placing an apostrophe in front will reverse this effect (somewhat). If your goal is to limit the chart data to just the last 60 rows consider a dynamic named range.

 

[Gordon K]

I tried double clicking on cell A19 and pressing enter. It did not recalculate to a number. The only way my chart looks the way I want it is if at least one cell in column A is text. That is baffling. I am including two screen shots. On one I cleared the contents of cell A18 and the chart looks like I would expect. On the other, I cleared the contents of A19 and the chart is very strange looking. Can someone explain what is happening?

 

[sheetspread]

Type =isnumber(a19) in an unused cell to see if it converted to a number. A19 is the only cell in that column formatted as text, when you change it to number the scale of the chart will change also. If you double click on the x-axis and choose text under axis type your chart will adjust as you want.

 

[Gordon K]

Are you able to delete A19 and get the strange looking chart? The only way I can change A19 from text to number is to delete the leading {'}. Thanks for showing me how I can format the axis text. I wanted the same format as my data so I checked the box.

I still do not understand why I need at least one "text" value in my data for the chart to look right.

 

[sheetspread]

Yes, if I delete A19 then everything is formatted as a number and the chart becomes scaled to the variable date/time differences (decimals, mixed numbers, negatives) instead of evenly spaced by 1. I don't see a leading apostrophe in your file, but that's just another method of formatting a number to text. Tell me if that still doesn't answer your question.

 

[Gordon K]

I used a custom format mm/dd/yyyy hh:mm to display date and time in my table and chart. Whether I used the exact same format in the chart or the check the box to link the formats, the chart is picking up the date but not the time. Rather than trying to resolve this I rewrote my macro to generate date and time as pure text. So that problem is fixed.

I am struggling with setting up dynamic ranges for my chart. I am trying the define the dynamic ranges using the Offsett and CountA functions, but not having success. Any suggestions or links to tutorials will be appreciated.

 

[sheetspread]

A scatter chart might present the times better, but if you like how everything looks with text then fine. Here's how I answered a similar question about dynamic chart ranges a few weeks ago:

http://www.mrexcel.com/forum/excel-questions/967012-chart-range-but-ignore-ending-data.html