dynamic charts with missing data in chart range

Formic Nether

New Member
Joined
Jun 5, 2012
Messages
20
Hello

So, I'm working with a raw data set that I have made, and I frequently update the data whenever I collect data from sources. The first two rows of the data set are frozen, so I can have some semblance of what my data titles are. Using the data, I have made several charts. Now, I know I should have realized that I needed to do some dynamic charting in order to not make my life miserable whenever I update my data set. However, now I'm in this predicament in which I do have a data set, but there is a large chunk that is missing.

I was looking at this website for dynamic charting, but the first thing I need to do is to define the names. The only thing that I know that I have no missing data is the date column because it is predefined to be every 15 minutes starting from my first data collection. The rest of the data may or may not have missing data.

Is there still any possibility of doing any dynamic charting?

Any assistance is appreciated.
 

Formic Nether

New Member
Joined
Jun 5, 2012
Messages
20
Well, apparently, I solved my own problem after doing some heavy experimentation.

The answer is that I can do dynamic charting. In order to solve the problem of missing data in the data set, I need to ensure that there is a consistent column in which I will never have any lost data. Apparently that is the date/time column. Using this as the reference value, I can then use the OFFSET formula for the following formula:

Code:
date=OFFSET('Sheet1'!$(date_column)$(starting_date_row),1,0,COUNTA('Sheet1'!$(date_column):$(date_column))-1,1)

data1=OFFSET('Sheet1'!$(data1_column)$(starting_data1_value),1,0,COUNTA('Sheet1'!$(date_column):$(date_column))-1,1)
This can be verified by going to the name manager and checking how the name references which column,row. It should refer to the same number of rows as the date column.
 
Last edited:

Forum statistics

Threads
1,085,589
Messages
5,384,624
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top