How to keep the trend and change the Max and Min?

[Archie-Medes]

Hi there
I've got this set of temeparture data for different months of a year:
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
11.0 12.2 15.7 18.6 22.8 24.3 25.6 26.4 23.5 20.8 16.5
12.3
with a minimum of 11.0 for January and a maximum of 26.4 for August. I'd
like to generate another set of temperature values based on this one but fix
my Jan temperature at 22.7 and my Aug temperature at 24.4. How would I find
the other 10 values without changing the trend of my yearly temperature
increase and decrease?

 

[Tushar Mehta]

You expect to generate a trendline that goes through Jan=22.7 when the
observed value for Jan is 11? Not to mention that Dec, Jan, and Feb
constitute the coldest months of the year!

But, if you must, the following algorithm might work...and I am sure someone
will correct me if I am off the mark...{grin}

The underlying premise of the algorithm used to calculate the conventional
trendline coefficients is "least squares." It essentially computes
coefficients A1 and A0 (for a linear trendline, A2, A1, and A0 for a
quadratic trendline etc.) as follows:

Let the number of observations be i=1..N. Let the desired trendline be of
order M. So, we need to compute the coefficients A0, A1, ..., AM.

The idea is to minimize the least squares error, i.e., minimize the
objective function SUM-over-all-observations-i=1-to-N ((Yi -
SUM-over-all-coefficients-j=0-to-desired-order-polynomial (Aj*Xi^j))^2)

This is easily solved by Solver. I imagine you can find examples of how to
implement the above by searching the Google archives of the Excel newsgroups
or by searching google for websites on the topic.

What you need to do is eliminate the 2 points corresponding to Jan and Aug
from the objective function and add them as constraints to the optimization
problem, i.e., SUM(Aj * X1^j)-Y1 = 0 and SUM(Aj * X8^j)-Y8 = 0 where the
index 1 represents January and the index 8 represents August.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu

 

[Archie-Medes]

Thank you Tushar and yes, I must. you see the first set is the actual
observed temperatures and the second set will include the desired temperature
in each month, which is by the way different from month to month (brcause of
the change in clothing and psychological behaviour etc). The standard I am
using suggests a 22.7C for winter optimal comfort and 24.4 for summer in a
situation like mine. This is kind of peculiar to jump up about 2 degrees in
comfort temperature from the last month of the winter to the first month of
the summer. Therefore, I decided to use a smooth line instead, a line
covering 22.7 in my coldest month and 24.4 in my warmest and moving up and
down along the same rythm of the actual data (obviously with smaller
fluctuations).
I appreciate your answer but it sounds too smart for a simple end-user like
me. Can we please talk in a more user-friendly way? Is there a menu I should
check or values in a dialogue box that needs changing or a formula I can
paste in my function bar?
Thanks again,
Archie

 

[Tushar Mehta]

Sorry if my suggestion was too overwhelming. What you want to do is
sufficiently non-standard that I doubt anyone has a readymade solution for
you.

But, why bother doing a trendline analysis. Given your explanation of
wanting to gradually change the temperature, would a straight-line change
work for you? If so, divide the difference in max and min by the number of
intervening months (7 for Jan-to-Aug and 5 for Aug-to-Jan) and that's how
much you should increase (decrease) the temperature each month.

I will try and post a smooth trend-like solution but things are incredibly
hectic this week and so...no promises.
--

Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu