Excel Forecast Function

[vyper78]

Hi

I've got two sets of data that I'm attempting to run some statistical analysis on. Col A contains different capacities of truck and Col B contains the rates at which they were loaded. I'm attempting to group the capacities into groups of 4, i.e: 0-1 tonne, 1-2 tonne, etc, and getting an avg for each group. IE: If a truck is 0.7t, it should take approx X hours to load.
I started grouping and getting an average for each group and then decided that this was no good as some of the data included some outliers.
I then decided to use the Forecast function but read that the formula assumes a linear relationship between the two sets of data, however I've worked out (via correlation) that there is _no_ linear relationship between the data.
Is there anyone out there that can guid me in what I'm trying to do ...
Hope this is in the right forum.

V.

 

[macropod]

Hi vyper78,

To find the average of a range in B1:B100 of a set of data for which the criteria are in A1:A100, you could use an array formula like:
=AVERAGE(IF((A$1:A$100<=1),B$1:B$100,))
or
=AVERAGE(IF((A$1:A$100>1)*(A$1:A$100<=2),B$1:B$100,))
In the latter case, the formula would return the average from column B of the values for which the corresponding values are greater than 1 and less than or equal to 2.

If you want to test the variability of the data, change 'AVERAGE' to 'STDEV'.

Cheers
PS: Array formulae are created with Ctrl-Shift_Enter

 

[ETID]

It sounds as though have done something similar to this.
not being a Mathematician, I cheated by doing this.

1st

I created a chart based on the x and y data...

Then added a polynomial trend (level 6 in my case because the data would normaly produce a curve of varying, but distinct parabola.)...being sure to select the option to show the equation on the chart.

then...

I run a macro to lift and disect the formula text from the chart and pop it into cell E20 as a real formula...

Sub GetEqua()
Dim Equa As String, EquaNew As String, LocEqual As Integer
Dim EqLen As Integer

ActiveSheet.DrawingObjects("Chart 1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
Equa = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
EqLen = Len(Equa)
EquaNew = ""
For X = 1 To EqLen
If Mid(Equa, X, 1) = "=" Then LocEqual = X
Next X
For X = LocEqual To EqLen

If Mid(Equa, X, 1) = "x" Then
EquaNew = EquaNew & "*E21^"
If Mid(Equa, X + 1, 1) = " " Then EquaNew = EquaNew & "1"
GoTo jump1
End If
EquaNew = EquaNew & Mid(Equa, X, 1)
jump1:
Next X
ActiveSheet.Range("E20").Formula = EquaNew' formula to resolve the value of the Y axis
Range("E21").Select 'type a value for X and cell"E20" to return the y value along the curve

End Sub