Making an Auto-Updating Graph with non-continuous data

[FarazM]

I'm trying to create a auto-updating graph for samples taken from water bodies in the area, I have about 5 columns, the first is the date (continuous), and the other 4 are the different water bodies. However samples aren't taken from every water body each time leaving me with gaps.

I've looked into this a bit and found defining names for columns and the OFFSET formula, however COUNTA does not work with gaps.

Additionally, I have dates entered without data yet that I don't want appearing on my graph, which is why I'm not using a table.

Is there a way to manipulate the OFFSET function, using MATCH, MAX or anything else to help me achieve what I want to do?

 

[thisoldman]

We'll assume the chart already exists and the data table looks like this:

	A	B	C	D	E	F

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ccccff"]Creek[/TD]
[TD="bgcolor: #ccccff"]Lake[/TD]
[TD="bgcolor: #ccccff"]Pond[/TD]
[TD="bgcolor: #ccccff"]River[/TD]
[TD="bgcolor: #ccccff"]Stream[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]01/01/2014[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]01/02/2014[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]01/03/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]01/04/2014[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]01/05/2014[/TD]

</tbody>

We have named ranges for Creek =B2:B6, Lake =C2:C6, and so on. Dates extend from A2 downward.

We'll put the last data point offset in Cell H2. The offset is calculated with Row 2:2 equalling 1.

Cell H2:
=MAX(MATCH(9.99E+307,Lake),MATCH(9.99E+307,Stream),MATCH(9.99E+307,Pond),
     MATCH(9.99E+307,River),MATCH(9.99E+307,Creek))

Enter the formula below in Cell H3. This is the range of dates we want to use as x values or x labels, depending on the chart type.

=OFFSET($A$2, , , $H$2)
​

I've omitted default arguments in the formula. The result will display as a date serial number. We don't care what value is displayed.

To check that this is the correct range of cells for x, copy the formula, not the cell, and paste that formula in the GoTo dialog's Reference: text box. Ctrl+g or F5, paste, then press Enter or click OK.

Edit the formula in Cell H3 to include the worksheet name for the cell references:

=OFFSET(Sheet1!$A$2, , , Sheet1!$H$2)​

If the sheet name includes spaces, enclose the worksheet name in single-quotes:

=OFFSET('My Data'!$A$2, , , 'My Data'!$H$2)
​

You can check if it is correct using the GoTo dialog as we did previously.

From here, you can use these two tutorials, from Peltier Technical Services, Inc., to create a dynamic chart:

The Excel Chart Series Formula
Create a Dynamic Chart
​