Trendline and its referencing / linking to a cell

[sudhanshu]

PLEASE HELP

I have not been able to receive help. Probably the question may not be
clear. I am once again putting up the question in detail for your
help.

I am trying to create a chart and trendline for a data set to be used
for interpolation/forecasting. The data is for Rel. humid. (Relative
Humidity) and Power Output (PO) for different ambient Temperatures. I
have data available for Power Output for 0 to 100 Rel. Humid. values
(in step of 10) for four different ambient temperatures viz. 0, 15, 30
and 45 deg c.

PLEASE NOTE THAT POWER OUTPUT (PO) DEPENDS BOTH ON HUMIDITY AND AMBIENT
TEMPERATURE.

Problem is find out Power Output for different rel. humid. values (for
any value between 0 to 100) and for different amb. temp. (any value
between 0 to 45 deg c.)

Following is the data set:

X-axis Y-axis ------------>>>>>
Rel PO PO PO PO
Humid @0deg @15deg @30deg @45deg
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

I tried to attempt above problem by plotting graph between Rel. Humid.
on X axis and Power Output (PO) for four ambient temperature on Y axis.
By doing above I have been able to plot four curves.

I have been able to automatically generate trendline for Power Output
(PO) for any input value of ambient temperature between 0 to 45 deg. c.


However, I am unable to link the automatically generated trendline
equation to a cell wherein it automatically gives result for any input
value of Rel. Humidity.

You may suggest any better method to solve above problem.

Other related problems are :

1) How to create a 3-D chart with above data set. Means Whether it is
possible from EXCEL to obtain trendline equation for two input
variables.

Presently we get equation in form of y=f(x), but whether it is possible
to get in form of z=f(x,y).

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

Thanking you in anticipation of reply
Sudhanshu

 

[Jerry W. Lewis]

Data as presented are exactly linear in relative humidity for each
temperature except 0deg. If the non-linearity at 0deg is real, then you
have not sampled enough low temperatures to model it. The 0deg data is
nearly linear (R^2=0.9988); If we just consider slopes, we get

temp slope
0 0.012618
15 0.02
30 0.008
45 0.02

which is nonlinear. Is there a theoretical basis for a particular
parametric model? If instead you want to fit a polynomial, the form
would have to be minimally quartic, but you have not sampled enough
temperatures to fit a 4th order polynomial.

I am somewhat skeptical of the saw-tooth nature of the fitted slopes.
Are you sure that the data as reported are correct?

Jerry

 

[Jon Peltier]

I'd answered this a few days back. Perhaps the post didn't propagate
correctly to ExcelForum? It's possible to fit a relationship to two or
more independent variables, but as Jerry points out, your data as posted
has some issues. I'd noticed that when I posted, but ignored it,
thinking maybe you tried sanitizing the data and switched a couple columns.

- Jon

 

[sudhanshu]

Thank both of you John and Jerry

As indicated in my problem earlier, that "POWER OUTPUT (PO) DEPEND
BOTH ON HUMIDITY AND AMBIENT TEMPERATURE AND IS NON LINEAR".

Problem is to calculate Power output (PO) at any intermediate value o
ambient temperature and Rel. Humid. lets say 32 deg. C. and 75% Rel
Humid.


Following is the exact data set for your perusal.

X axis Y axis ------->>

Rel. PO@0 PO@15 PO@30 PO@45
Humid.

0 0.99900 0.99740 0.99448 0.99020
10 0.99918 0.99784 0.99540 0.99200
20 0.99930 0.99830 0.99642 0.99380
30 0.99946 0.99872 0.99740 0.99550
40 0.99962 0.99920 0.99830 0.99710
50 0.99980 0.99960 0.99920 0.99856
60 1.00000 1.00000 1.00000 1.00000
70 1.00020 1.00042 1.00092 1.00150
80 1.00040 1.00082 1.00170 1.00288
90 1.00056 1.00126 1.00250 1.00420
100 1.00066 1.00172 1.00330 1.00556


I tried to attempt above problem by plotting graph between Rel. Humid
on X axis and Power Output (PO) for four ambient temperature on
axis.

By doing above I have been able to plot four curves. I have been abl
to automatically generate trendline for Power Output (PO) for any inpu
value of ambient temperature between 0 to 45 deg. c.

However, I am unable to link the automatically generated trendlin
equation to a cell wherein it automatically gives result for any inpu
value of Rel. Humidity.

You may suggest any better method to solve above problem.

Other related problems are :

1) How to create a 3-D chart with above data set. Means Whether it i
possible from EXCEL to obtain trendline equation for two inpu
variables.

Presently we get equation in form of y=f(x), but whether it is possibl
to get in form of z=f(x,y).

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

Thanking you in anticipation of reply
Sudhansh

 

[Jon Peltier]

Oh, if all you need is to interpolate, make sure you have real values
for temperature (just the numbers 0, 15, 30, 45 in the cells), and try
the interpolation function Tushar Mehta posted:

http://www.google.com/[email protected]

- Jon

 

[Jerry W. Lewis]

If you select the 5 columns and plot as an XY (Scatter) plot, you will
get 4 relationships with between power output and humidity, one for each
temperature. Each relationship is essentially linear. If you plot
these slopes against temperature, you get an essentially perfect
quadratic fit. The intercepts can then be computed by observing that
power output is always 1 at 60% RH.

Jerry

 

[MikeG]

-----Original Message-----
PLEASE HELP

I have not been able to receive help. Probably the question may not be
clear. I am once again putting up the question in detail for your
help.

I am trying to create a chart and trendline for a data set to be used
for interpolation/forecasting. The data is for Rel. humid. (Relative
Humidity) and Power Output (PO) for different ambient Temperatures. I
have data available for Power Output for 0 to 100 Rel. Humid. values
(in step of 10) for four different ambient temperatures viz. 0, 15, 30
and 45 deg c.

PLEASE NOTE THAT POWER OUTPUT (PO) DEPENDS BOTH ON HUMIDITY AND AMBIENT
TEMPERATURE.

Problem is find out Power Output for different rel. humid. values (for
any value between 0 to 100) and for different amb. temp. (any value
between 0 to 45 deg c.)

Following is the data set:

X-axis Y-axis ------------>>>>>
Rel PO PO PO PO
Humid @0deg @15deg @30deg @45deg
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

I tried to attempt above problem by plotting graph between Rel. Humid.
on X axis and Power Output (PO) for four ambient temperature on Y axis.
By doing above I have been able to plot four curves.

I have been able to automatically generate trendline for Power Output
(PO) for any input value of ambient temperature between 0 to 45 deg. c.


However, I am unable to link the automatically generated trendline
equation to a cell wherein it automatically gives result for any input
value of Rel. Humidity.

You may suggest any better method to solve above problem.

Other related problems are :

1) How to create a 3-D chart with above data set. Means Whether it is
possible from EXCEL to obtain trendline equation for two input
variables.

Presently we get equation in form of y=f(x), but whether it is possible
to get in form of z=f(x,y).

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

Thanking you in anticipation of reply
Sudhanshu

equation" then drag the left clicked mouse over the entire
equation. edit copy and paste into a cell. It's text so
you have to change the "x" for *your cell^ (eg *AE53^).
You can do it manually of using "edit find replace" but
that leaves a ^ at the end. Then delete the "y" and it
should work. Be careful unless you chooose a polynomial
with a high order (I always choose the max of 6) and
format the equation text box to "number" 10 decimal
points, otherwise you end up with an equation that doesn't
track youe data.

Mike