Plotting Formulas

**EEW** · 02-19-2017, 09:26 PM

Hi,

I hope this question has not been posted before.
I would like to plot a table using three data ranges that I already have on my spreadsheet.

I have 3 temperature data sets; 2 sets from upstream and one set from downstream.

I would like to plot three different lines on my graph:
-the downstream temp.
-the mean of the two upstream temps + the standard deviation time 3.
-the mean of the two upstream temps minus the standard deviation time 3.

My problem is that I want to do this without having to pre do the calcs on my spread sheet. I want to keep it all very neat on still only have the three data sets on display.

Could I do this in a macro?

the point of this would be to be able to visually see when the downstream temp fall out of the given boundary.

I hope I have explained this clearly.

thanks in advance for any help or advice.

**jeffreybrown** · 02-19-2017, 09:35 PM

Hello EEW & Welcome to the Forum,

Honestly, you'd be better served showing us what you have now (in a workbook) and maybe we can work out a modification to fits your needs. Making up your entire data set doesn't make sense from my perspective.

Is your question how to format the data or actually build the chart?

**EEW** · 02-19-2017, 10:48 PM

Hi JeffreyBrown,

Sorry, I thought my post may have been a little confusing. I have attached a sample of my data, and a picture of what I would like my graph to look like. I only want to be able to graph the red line and the two outermost dotted lines. My problem comes as I would like to be able to plot this graph without having to pre do the calculations. can I just write the formulas into the plot somehow?
Data Image.JPG Plot.JPG

**MrShorty** · 02-20-2017, 12:40 AM

My problem comes as I would like to be able to plot this graph without having to pre do the calculations. can I just write the formulas into the plot somehow?

It might depend on exactly what you mean by "pre-do" the calculations. I like to think that just about anything is "possible" with enough time, effort, and ingenuity.

In this case, you need to know that you cannot somehow write the formulas into the series definition. There are two options for defining the series, values stored in ranges, or values stored in an array constant. With a minimum of effort and ingenuity, I see two possibilities:

1) Pre-do the calculations in ranges. This is the option you say you don't want to do. IMO, this is by far the easiest option. If you really don't want to see the columns containing these calculations, then you can hide these columns.

2) Pre-do the calculations in VBA and store the results in an array. Then write that array into the .Values property of the series (example here: https://msdn.microsoft.com/en-us/lib.../ff197014.aspx ).

Either option involves pre-doing the calculations. Are you certain you do not want the spreadsheet to pre-do the calculations for the chart?