Real-Time Chart (a moving window of time)

Hello World!

To state my problem simply:

In Excel 2013, I have a line graph which charts real-time data (incoming from industrial humidity/temperature/dew point transmitters). It expands to accommodate incoming data from the receiver, but I want to be able to have the first entry displayed on the graph to be variable, essentially dragging along as new data is added. Thus, a constant window of, let's say, 50 data points which will include the most recent data point and the 49 previous ones ALWAYS - a real-time moving window.

I've tried using the COUNTA function for the initial row-value of the x-axis on the graph which basically counted all rows in column A (my date-time X-axis which I want to "drag along") and then subtracted 50 from it. However, I get an error no matter what...

Example1: =Sheet1!$A$COUNTA(Sheet1!$A:$A)-50, $A$100
Example2: =Sheet1!$A$(COUNTA(Sheet1!$A:$A)-50), $A$100

My hope was that the newest data would be displayed via $A$100 (and all data between newest and initial, obviously)
and my initial point would, in this case, be row 50. Then, when the next row of data comes in my newest data point would be row 101 and my initial point would be 51,
keeping a 50 point interval in view on the graph at all times.

This, however, has resulted in failure and disappointment...

Any ideas??

Thank you for your time!

Unfortunately, it says "The formula is invalid."
It seems to do that anytime I don't have "Sheet1!" (without quotes) directly after the = sign.\

Edit: I added "Sheet1!" (without quotes) after the equal sign, but got the same error message I was before...

Hello MCS89 and Welcome to Excel Forum.
Perhaps this will help.
Column A is a mock up of humidity readings.
Cell B1 displays the first row of the most recent 50 rows of data using: =LOOKUP(2,1/(A2:A1000),ROW(2:1000))-49
B2:B110 identify the most recent 50 humidity readings using: =IF(AND(ROW()>=B$1,A3<>""),ROW()-B$1+1,"")
C2:C51 display the most recent 50 humidity readings using: =INDEX(A$2:A$110,MATCH(ROW(1:1),B$2:B$110,0))
The graph (scatter plot) takes the values from column C. The x axis values are manually set from 1 to 50.
Test by adding a value into cell A102 and/or deleting the value in cell A101
Let us know if you have any questions.

Wow, this was SO CLOSE to working for me. Thank you for the reply!!! The chart did like it was supposed to do; however, starting with row 50 of the moving window, the value is replaced with NOTHING... No idea why. So, after my first new reading, I still have rows 1-49. After the second new reading, I only have 1-48 (still in their correct places, but with a blank space where the 50 and 49 values should be...and so on with each new reading... This affects my four chart series values (y1-4) that are dependent on the 1-50 values.

Your B1 is my U1. =LOOKUP(2,1/(A5:A100000),ROW(5:100000))-49
Your B2:B110 is my V5:V100000. =IF(AND(ROW()>=U$1,A1323<>""),ROW()-U$1+1,"")

Your C2:C51 is my:

W106:W155 (x-axis) =INDEX(A$5:A$100000,MATCH(ROW(1:1),V$5:V$100000,0))
X106:X155 (y1) =INDEX(F$5:F$100000,MATCH(ROW(1:1),V$5:V$100000,0))
Y106:Y155 (y2) =INDEX(J$5:J$100000,MATCH(ROW(1:1),V$5:V$100000,0))
Z106:Z155 (y3) =INDEX(N$5:N$100000,MATCH(ROW(1:1),V$5:V$100000,0))
AA106:AA155 (y4) =INDEX(R$5:R$100000,MATCH(ROW(1:1),V$5:V$100000,0))

Any idea why this would happen? It doesn't happen with yours

I am using a dynamic x-axis as described (above). That's the only real difference I can see between your worksheet and my ever so slight modification of it.

04/02/18 12:31:43 <-- That's what the values are like in my A column, my x-axis. They're in the Text format.

Thanks, again, for your time!

Edit: Could it be a time issue? I'm having to use a 32-bit laptop, so it's slow from the get-go... maybe the U1 value isn't updating fast enough for each new entry or something??? I have no idea. *cries*

Edit2: Well, I thought I had it figured out ... I thought MAYBE it's the way the incoming data system is set up causing the row to go blank. It seems like the final row (newest, incoming row) puts data where it goes and clears ALL columns on the row to the right of the last reading. To get around this, I moved the 1-50 (IF statement functions) to a 2nd sheet in the correct row and linked it appropriately -- Sheet1! where needed. The 1-50 values showed up correctly, but it has the exact same problem as when it's on the first sheet...

I doubt that it is a time issue. Check the bottom left corner of the spreadsheet and make sure that the word Calculate isn't displayed. If so select the word Calculate, or press the F9 key. Then go to the File tab > Options > Formulas and select Automatic under Calculation options.
Here is my interpretation of a sample of your file to show that what you are doing should be possible. To really help we would probably need to see a sample of your workbook.
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window. Make sure there is just enough data to demonstrate your need. Remember to desensitize the data, if necessary.
Let us know if you have any questions.

Okay... Even when I manually add a new entry (say, copy and paste a previous time or just type some value) into the A column it clears the 50. The next time I add a value, the 49 also disappears and so on. It can't just be the incoming data system. It DOES clear ALL columns on the row of the incoming data to the right of the last entry on the row when the data is automatically incoming. However, when I manually add a new entry into the A column it only clears the 50, then 49, then 48 values, but does NOT affect columns to the right like with automatic incoming data (while logging data.) NO idea why that is.

I've attached a version which ONLY has the U1 cell input (lookup function) and the 1-50 values (If statements) on the last 50 readings. I removed my charts and other stuff to make the attachment adhere to the size limit.

Thanks, again, for all your help!

Add a value to the A column and then be sure to have some padding on your keyboard for when your head hits it. XD


Edit: I was able to make it work with manual input by dragging the formula down a few rows. Sadly, this doesn't work for automatic input. I tried moving the column of 1-50 values WAYYY over to the right in hopes the "wipe" has a horizontal limit. It does not... Today is the last day I have to work on this. Tomorrow, I gotta bring whatever I have to the client. May God have mercy on my soul.

In the last installment the issue with column V is that the formula is only copied down as far as row 1357. Paste the following in V5 and copy down to V2806:

Formula:

Please Login or Register  to view this content.

I tested by both copying and typing date/time into A1358 and both worked.
Let us know if you have any questions.

Just an example, based on the Book1(MCS89) file, of how you might set things up if you wanted to leave the chart on sheet1.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.