Need formula to calculate trend for the high values in data.

[HammerJoe]

Hi,

I am tracking on a daily basis some data.

Ie:

2007/07/10 10.1
2007/07/11 10.8
2007/07/12 12.2
2007/07/13 11.9
2007/07/14 10.5
2007/07/15 12.1
2007/07/16 10.2
2007/07/17 11.5
2007/07/18 12.00
2007/07/19 11.9

If we look at this data we can clearly see that the highest numbers
are decreasing over time (12.2,12.1,12,11.9) so drawing a line in a
graph for this trend would show that it is decreasing (safe to guess
the next highest number would 11.8 at some point in the future).

I need a formula to calculate this.
Trend() does not work the way I want it.
Is there another function that does this or anyone has any idea for a
formula?

Thanks

 

[Gary''s Student]

Just fitting your peak data to
y = A*x + B

where x = 1,2,3,...
yields
A = -0.033324303
and
B = 12.29995419

applying this line gives us:


2007/07/10 10.1
2007/07/11 10.8
2007/07/12 12.2 12.19998128
2007/07/13 11.9
2007/07/14 10.5
2007/07/15 12.1 12.10000837
2007/07/16 10.2
2007/07/17 11.5
2007/07/18 12 12.00003547
2007/07/19 11.9
2007/07/20
2007/07/21 11.90006256
2007/07/22
2007/07/23
2007/07/24 11.80008965
2007/07/25
2007/07/26
2007/07/27 11.70011674



If a max occurs on a date, the next max will be three days later. The value
at that later date will be approximately .1 less

 

[Dana DeLouis]

Hi. You didn't say, but it sounds like you are breaking your data into
3-day blocks, and taking the Max of that group.

=MAX(A1:A3)
=MAX(A4:A6)
=MAX(A5:A7)
etc...
Then, use Trend on this newer data.

 

[HammerJoe]

Thanks for the help, but how do I transfer that to Excel as a formula?
Or maybe you can tell me what Y A X and B apply to in the data.

Thanks

 

[HammerJoe]

Hi,

Thanks for the reply.
I only used that as an example, the actual data is much longer. But
your idea is interesting.
Split the info into blocks and get a trend from it... interesting.

I wonder if theres something simpler.