Need Help Creating Run Chart Template with Potentially Missing Data

[jcartwright0000]

So, I've been searching and searching and can't seem to find any answers. I hope to find the answer here, even if it's something really simple not requiring any extra formulas.

I am trying to create a RUN CHART TEMPLATE for a not-so-excel-savvy colleague. She would just need to plug in numbers and BOOM...she has a nice run chart. The run chart will track process observations, but the problem is that she will not be doing observations every single day. Therefore, you have blank cells and the line chart is skewed because it can go from 0% to 100% in a blink of an eye, which also throws off the average. I've tried using the TREND formula, the NA() formula, and it works great BUT my not-so-excel-savvy colleague can't be entering that every month. Nor do I want to enter it every month for her (I would gladly do it, but she probably doesn't want to wait on me to complete it for her). I can also understand if there is just no other way, but I need to know that before I continue down a path only to find out it's not possible.

Attached is data collected from August without tweaking it so run chart is satisfactory. But, I need to figure out a way to make a template for future months. There would be no way of knowing which dates in future months where observations will be conducted.

HELP!!

[sweep]

Hi,

To exclude the blanks you could do the following

1. Create a named range (e.g. data) that describes E4:E34

2. Create another named range (e.g. graph_data) that is defined by =IF(ISERROR(data),NA(),data)

3. Plot the chart using graph_data as the y data source.

Repeat for all data sets


Please see attachement

[jcartwright0000]

Thank you so much! You helped me prevent patient falls!! Lol