Creating chart with two vaiables, Trendline and its referencing / linking to a cell

[sudhanshu]

HI

I am trying to create a chart and trendline for a data set to be use
for interpolation/forecasting.

Following is the data set:

X-axis Y-axis ------------>>>>>
Rel Humid PO PO PO PO
@0deg @15deg @30deg @30deg
0 1.0000 1.2 1.4 2
10 1.1000 1.4 1.48 2.2
20 1.2000 1.6 1.56 2.4
30 1.3300 1.8 1.64 2.6
40 1.4600 2 1.72 2.8
50 1.5900 2.2 1.8 3
60 1.7200 2.4 1.88 3.2
70 1.8500 2.6 1.96 3.4
80 1.9800 2.8 2.04 3.6
90 2.1100 3 2.12 3.8
100 2.2400 3.2 2.2 4

The chart is created with Rel. Humid as X axis and Power Output (PO) a
various ambient temperatures (specifically for 0, 15, 30 and 45 degree
is plotted) on Y axis.

Following are the problems :

1) How to create a 3-D chart with above data set.

2) How to obtain an trendline equation for “Power Output” wherein tw
inputs are required viz. Rel. Humid. and Amb. Temp. ie. How to kno
Power output at lets say at 32 degrees and rel. humidity of 75%.

3) Is there any way to link the trendline equation to a set of inpu
data and automate the process.

Thanking you in advance.
Sudhansh

 

[Jon Peltier]

1. Your data is set up already for a 3D chart. I actually just used this
as the data:

0 15 30 45
0 1 1.2 1.4 2
10 1.1 1.4 1.48 2.2
20 1.2 1.6 1.56 2.4
30 1.33 1.8 1.64 2.6
40 1.46 2 1.72 2.8
50 1.59 2.2 1.8 3
60 1.72 2.4 1.88 3.2
70 1.85 2.6 1.96 3.4
80 1.98 2.8 2.04 3.6
90 2.11 3 2.12 3.8
100 2.24 3.2 2.2 4

The blank in the top left helps Excel find the categories and labels in
the first row and column of the data; otherwise having numeric data down
the left column and across the top row might confuse Excel. Select the
data, start the chart wizard, and make a 3D Column chart or a Surface
chart. I even found a line chart, by columns, was useful.

2. To get a trendline with factors RH and Degrees, you need to set up
the data in three columns:

RH Deg P.O.
0 0 1
0 15 1.2
0 30 1.4
0 45 2
10 0 1.1
10 15 1.4

Then you can use the LINEST worksheet formula to get your regression line:

Y = 0.821 + 0.015155 RH + 0.02537 Deg

You can use Data Analysis from the Tools menu, and run the Regression
module, which gives the same answer as above.

3. You could probably run the Regression analysis from VBA; it's part of
the Analysis Toolpack, which has a VBA interface. The LINEST formula
will update itself if new data is pasted into the range. Use dynamic
ranges if the size of the data range will be variable.

- Jon