[janguera]

Hello!
I'm trying to figure out this problem: iI'm given a maximum and minimum value for 11 consecuitive values, and these values when plotted fit an exponential curve. So for example the values are:
21.361
18.136
16.029
13.672
12.793
12.394
11.143
11.090
11.015
10.873
10.270


I'm trying to set up an exponential statistical contrast here which basically means that I'm trying to assign values to each of the aforementioned data points that when plotted would decay in an exponential function at a similar rate/slope as the data plotted above (same trendline equation) AND if these weighted values are summed together they add up to zero. I know that it is importatnt to consider tau (where the exponential curve decays to ~37% its original value) for my values, but I'm getting stuck assigning weighted values.

Any help would be greatly appreciated!

Joaq

[joseph2000]

You can use the what's best modelling of the lindo product. This is an add-in for ms excel. You can point to sum of the reference cell equal to zero... and the values will be generated as long as you defined its correct slopes and other parameters.

Joseph2000

[janguera]

Thanks, I've been playing with What's best for the afternoon learning how to use it and now have a couple of questions so that I'm not here all night!

-I'm guessing that I'd have to put in a maximum and minimum values...

-Likewise, the shape of the curve would be based on the equation
y = 23.855e-0.1308x for the values below... I'm just not sure how to set this up next... if you (or anybody) is knowledgeable regarding this program, please let me know!

Joaq

[joseph2000]

Hi!!

IF what you want is a parallel line relative to the given line then the slope must be equal to this line. You can use the required values using this for the coordinates of points. May I see your problem so that I can play with it too. YOu may also use solver found in add-in of excel, though it is less powerful.

Joseph2000.

[janguera]

Hello Again!
I'd be happy to describe the problem more in-depth if it leads to an answer, as my trying to model it in What's Best has been futile to this point!

So given the (11) values that I first posted, when I fit an exponential trendline to these points (after graphing them in EXCEL), I get the equation of
y = 23.855e-0.1308x. So what I need to do now is come up with arbitrary weighted values for these points that if I were to plot them would:

a) give me the same exponentially degrading shape as my original data points if they were plotted.
b) these weighted values when summed together add up to zero. So the first data point should be given 'more weight' than the second, the second 'more' than the third, etc.
Here's an example of what these weights would look like:

8.563
2.242
-0.084
-0.939
-1.254
-1.370
-1.413
-1.428
-1.434
-1.436
-1.437

The aforementioned weights were assigned to the following data set:

24.234
20.531
19.861
16.593
15.406
16.192
15.274
14.424
14.554
14.115
15.026

I don't know what the equation for the trendline was in this case, but maybe this knowledge will help me help you! Thanks again, I hope that this makes the question that much more clear!

Joaq

[joseph2000]

Hi I hope this is the solution. The answer is:

4.83468
3.867744
2.900808
1.933872
0.966936
0
-0.966936
-1.933872
-2.900808
-3.867744
-4.83468

I solve first the slope of the given line. I used the x component starting from 11 to 1. By basing on the slope of the line I used the goal seek feature of ms excel. I need to find the correct intercept which will give the sum to be equal to zero.

There is something wrong with this:
1, the assumption is that the line will be straight.
2. I had not considered the residual.
3. I had not considered what other trend is possible.

I can do some refinements when I test it with other model that will give the best fit.

Joseph 2000