Plot a graph from one single cell

[holiday]

--------------------------------------------------------------------------------
SORRY I AM REPOSTING THIS ONE AS I REPLIED TO MY OWN ONE YESTERDAY WHICH MAY MAKE SOME PEOPLE NOT READ/REPLY TO IT AND I REALLY NEED THE HELP!!!


Hi

I really need some help

I need to know, can I plot a graph (ongoing plotting each value) from just ONE single cell which constantly updates it's value? (I have a third party datafeed hooked up to one single cell in Excel 2000 [yes, the old one ] and it updates quickly in realtime automatically throughout the day). But I need to trace the history of the values, plot a graph of all the values which ever show up in that single, in other words.

Also, I need to date and time stamp each new plot on the X axis of the graph (only when a new figure appears in the cell). So, for example, my one and only "datacell" (say, A1!) which is linked to an external datafeed is showing "145.87541" at 12:30:45 on 27/04/05. I need that time and date on the X axis which the number as the plot. Then that cell changes value 2 seconds later to "145.87587" at 12:30:47 on 27/04/05. Now I need that second plot on the graph at the correct time/date stamp so I am constantly plotting the history of the value onwards (potentially forever!) using just that one, automatically updating, cell. THANKS!!!

Oh PS: if anyone actually knows how many plots my graph could possible ever have as a maximum in Excel - please could you tell me?! THANKS AGAIN!!!!!

 

[Oaktree]

Please note that, if you are the only person to reply to a post of yours, it will still show up in the "unanswered posts" filter. If it has been a reasonable amount of time since your last post, you can always bump (by replying to your thread) your post to the top of the list.

When you say "...Then that cell changes value 2 seconds later..." how does the value change? Does someone key in a different value? Or is it an automatically refreshing query?

One approach to this is to use a (VBA macro) Worksheet_Change event (or Worksheet_Calculate or similar, depending on how your cell is updated) to, every time the value in the cell changes, copy the cell and append its value and timestamp to a list on a hidden sheet or somewhere on the current sheet or whereever you'd like... somehow, you need to have a list of the values somewhere in your workbook so you can reference them in your graph.

Then, assign a dynamic named range to your list of values, and use the named range as the basis for your graph. That way, when a new value is appended to the list, your graph will automatically expand to include it.

Make sense?

 

[holiday]

Ahhh

Hey

Thanks for replying so quick!

Ok when I said "2 seconds later" I was referring to an automatically updating single cell which has a third party datafeed connected to it of realtime values. This is simple imported continuously into just one cell. The moment the value changes on this datafeed, it appears in that cell and the previous value(s) are gone forever!!!!! What I was hoping for was a simple way to just ask Excel to keep a record of these values in a graph ONLY (not invisible values plotted somewhere to reference from). Why? Because I am worried I will run out of room in a single column - I understand Excel can only record approx 64,000 cells in one column?? Supposing I left this worksheet open and running with live datafeed for a couple of days and it had to cope with, say, 100,000 updates? Could I make the amount of updates "unlimited"? (If so, I would not care if they were plotted invisible or even visible somewhere in the worksheet) - they key here is I need to track a historic plot of just one cell which updates automatically thorughout the day, anything ftom a couple of times a second to a couple of times an hour!!

I hope you can help - I hope Excel can do this!!!

 

[holiday]

PS!!

PS forgot to say:

1 - Each plot must always have a full time and date stamp, even if just a second apart.

2 - I am using good old Excel 2000 but am willing to upgrade to 2007 if I need to for this project I'm talking about on this forum!!

3 - Because I am a newbie and quick thick (as people go) I reckon I probably will end up ringing Mr Excel up for 1-to-1 help LOL!!!

THANK YOU!!!!!!!!

 

[holiday]

LOL

And notice I just wrote "quick thick" instead of "quite thick" as a testament to just how thick I can be.

LOL and thank you again!

 

[gardnertoo]

65536 rows x 256 columns = 16.7 million cells on one worksheet. Times (how many worksheets are allowed??) = LOTS of room.

I'm doing something similar, but on a shorter timescale. I mine historical process data at a power plant, which sometimes runs to the hundreds of thousand points. I use a pair of counters in the macro, one for column and one for row. When the counters read 1 and 31355, the data goes in cell A31355. I roll over the counters when the second counter reaches 32000 (32000 because that's the max number of points allowed in a graphed data series) so at the next data point the counters go from 1 and 32000 to 2 and 1, sending the data to cell B1.

 

[Oaktree]

gardenertoo's suggestion explains how to handle more than 65536 (1 column) data points... but, putting 100,000 data points on a chart doesn't seem like a smart idea to me (what will 100,000 tell you that 1,000 can't?), especially since it will be unbearably slow to redraw if you change anything/recalculate/etc.

Another thought: presumably, the software that's updating your query probably keeps track of the values it's sending. Have you contacted the data vendor to see if it is possible to access (or be sent) the data tables every day/hour/whatever time period you need? You might be recreating the wheel here...

 

[holiday]

Gardnertoo: thank you for clearing up the question about how many values I can store - so basically a macro can be written to say when I run out of cells in one column, move to the next. (And then when I run out of cells totally in one worksheet, move onto the next worksheet - effectively giving me as good as unlimited cells - COOL!!!!) (Although I am looking to use probably a max of only up to a million or so ever).

As for plotting 100,000 points on a graph, I'm sure I wouldn't actually do that - what I would most likely do then is store the constantly updating data, visible, on a worksheet. So it begs the question, can I make a chart display only the last X amount of plots, whilst being constantly updated (say for example, only display the last 500 plots or something like that? So each time a new value it added to the chart, the value 501 plots ago is no longer shown).

As for reinventing the wheel ( ) I'm really not trying to - the thing is I need each and every value change to caught by Excel in that single cell I was talking about (which could be more than once a second sometimes!), and now I realise yes I will have to store it in a worksheet, that is fine!! And then, hopefully, the last X amount will be constantly plotted and updated on the line chart. Actually I'm gonna be doing this on more than one data input - I will be doing it on about 10 data inputs in total, so I will have 10 worksheets capturing the data, all linked to 1 (yep, just 1!) graph ploting all the lines together. Phew! Well that's the plan anyhow!!

One last question (regarding what I have just written) - if I have 10 lines all being plotted on one chart, these will be taken from automatic data updates from 10 individual single cells (all hooked up to my external datafeed). But they will NOT be updating always in unison - they can update whenever, regardless of what the other 9 are doing. So if, for example, "cell number 5" got a new value sent to it from my datafeed and naturally updated the chart value, could I have all the other lines (which perhaps have no update at that exact moment) keep their current value and simply plot one space to the right on the chart along with the one that DID have an update to record (date/timestamped as well) so I don't have 10 lines which are all ending at different points in time. I would therefore have 10 lines which will ALWAYS be plotted on the same horizontal plot together, whether 1 cell updated on its own or 7 cells all updated at same time etc... I hope I made it clear?!!! (If I did make it clear, you can probably see my concern about how many plots I can have on a chart - I figure that this is going to be a very "active" Excel setup lol!!)

THANK YOU THANK YOU THANK YOU!!!!!!!!!

 

[gardnertoo]

The short answer: Yes, all the things you want to do with your data can be done, and without too much difficulty. One thing I don't know though: your version of Excel may not support some of the methods people are going to suggest for you.

The long answer: To get just the last X values to plot, one method is to create a named range and define it with OFFSET commands. For example, if you have data in cells B1:B12345, a range named "Sheet1!graphvalues" could be defined as follows:

=OFFSET(Sheet1!$B$1,COUNT(Sheet1!$B:$B)-10,0,10,1)

This counts how many values exist in column B, starts at the cell 10 rows up and zero columns right of the last cell, and includes a range measuring 10 rows by 1 column.

You could get even trickier. Say you want the ability in the future to change from 10 data points on the graph to some other quantity. Instead of hard-coding the number 10 in that name definition, you could replace the 10 with a cell reference:

=OFFSET(Sheet1!$B$1,COUNT(Sheet1!$B:$B)-Sheet1!$A$1,0,Sheet1!$A$1,1)

and type the 10 cell $A$1. Then later, when you decide you need to see the last 5,241 data points, you type 5241 in A1 and the named range self-adjusts and the graph changes too.

Your OFFSET formula will need to be a little more involved than the two examples above due to the column wrap you'll probably use, but it's definitely doable.

To get non-simultaneous data to plot as if it were simultaneous, your data storage macro will have to create a second column containing time-stamps with each discrete data point, and whenever the macro is triggered by the Worksheet_Change event suggested by Oaktree, it must gather data from all of your sheets at once. If your data points change at different rates, you'll end up with some of them having the same data point copied to several consecutive timestamps, but that's OK. Each of your ten data series could use the same ten timestamps as the X values, with each data series (the Y values) being a named range from a different sheet, for example Sheet1!graphvalues, Sheet2!graphvalues, etc, or something more descriptive (my vote) like "Sheet1!temperature", "Sheet2!pressure", "Sheet3!pump1runhours", etc.

 

[holiday]

Thank you so much for taking the time to help - I have the old 2000 version but was thinking of just upgrading to 2007 anyway for this project and so probably will now.

Yes, having numerous plots keeping the same value whilst others change (forcing all 10 plots to move one step forward on the chart) is fine, as you say - that's exactly what I need.

I am lost about OFFSET and the like at the moment because I really really am a newbie - but hey that's what the Excel help file is all about right?! So I'll do a bit of studying this weekend and have a go at getting this working on 2000. If I need to, I'll get 2007. And if I get stuck (which I am SURE I will!!!!) I know that there are cool generous people out on this forum who do actually help!!

Thanks to you gardnertoo and Oaktree - if I need to follow this up I;ll repost more confused questions

Holiday!!!