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

  • Thread starter Thread starter HammerJoe
  • Start date Start date
H

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
 
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
 
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.
 
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
 
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. :)
 
Back
Top