The following statistical functions are provided which operate on a range or a list of several slots. Note that lists can include arrays. There are examples of the statistical functions in the PipeDream Examples archive.
AVG(list)
Returns the arithmetic mean value of list.
Only numeric elements are evaluated so that blank slots do not upset averages.
COUNT(list)
Counts the number of numeric elements in list.
GRAND(m,s)
Returns a random number with gaussian distribution, mean m and standard deviation s.
If m is not given, PipeDream assumes 0; if s is not given, PipeDream assumes 1.
GROWTH(LOGEST_data, x_data)
Takes the result of the LOGEST function (LOGEST_data) and applies it to x_data to return estimated y_data.
LINEST(known_y's,known_x's)
Calculates the formula of the straight line which best fits your data (known_y's and known_x's) and returns an array.
The result is an array of 2 values giving {c,m} for the straight line fit
y = mx + c
where the first array element, c, is the y-intercept value (ie where x=0) of the best-fitting straight line and the second array element, m, is the gradient of that line.
An example is given below. LINEST is used to calculate the gradient and intercept of the best-fitting straight line through the data points. INDEX is used to extract separate elements of the result array.
A | B | C | D | |
---|---|---|---|---|
1 | 2.00 | 4.00 | -2.00 | C1=linest(B1B5,A1A5) |
2 | 3.00 | 7.00 | 3.00 | C2=index(C1,2,1) |
3 | 5.00 | 13.00 | ||
4 | 7.00 | 19.00 | ||
5 | 10.00 | 28.00 |
To verify the results, a scatter chart of the data series can be drawn and a line of best fit may be specified to be drawn on the chart.
The result is an array of m+1 values such that
y = a0 + Σi=m(ai·xi)
where the xi are the independent x variables.
An example is given below. It is assumed that the value of a house is related to the garden size, number of bedrooms and distance from the city centre. Values for eight houses are known and these are used to calculate (in A10) an array of the coefficients. Expanding the array into the slots A12 to C12 shows the resulting formula to be
Value = 40688 + 62*Garden + 3021*Bedrooms - 956*Distance
This is then used to calculate a value for a house with a garden of 600 square metres, 4 bedrooms and 2 miles from the city centre. D14 and D15 show two ways of calculating the result.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Garden m2 | Bedrooms | Distance | Value | |
2 | 1600 | 4 | 4 | 150000 | |
3 | 450 | 3 | 67000 | ||
4 | 50 | 2 | 0.5 | 55000 | |
5 | 0 | 2 | 1 | 45000 | |
6 | 650 | 4 | 3 | 100000 | |
7 | 200 | 1 | 3.5 | 58000 | |
8 | 250 | 4 | 55000 | ||
9 | 620 | 3 | 2 | 75000 | |
10 | 40688 | A10=linest(D2D9,A2C9) | |||
11 | 2 | A11=set_value(A12C12,index(A10,2,1,3,1)) | |||
12 | 62 | 3021 | -956 | ||
13 | |||||
14 | 600 | 4 | 2 | 87949 | D14=trend(A10,A14C14) |
15 | 87949 | D15=sum(A12C12*A14C14)+A10 |
LOGEST(known_y's,known_x's)
Calculates an exponential curve which fits your data and returns an array which describes the curve.
The equation for the curve is:
y = b · (mx)
or
y = b · Πi=m(mixi)
where the xi are the independent x variables.
This function is simply a logarithmic version of LINEST.
MAX(list)
Returns the maximum value in list.
MIN(list)
Returns the minimum value in list.
RAND
Returns a random number in the range 0 to 1 from a sequence which is the same each time PipeDream starts.
RAND(0) generates a random number in the range 0 to 1 using a random seed.
RAND(number)
Generates a random number in the range 0 to 1 using number as the initial seed.
STD(list)
Returns the standard deviation of the values in list.
This is the square root of the variance. See VAR for the equation used.
STDP(list)
Returns the population standard deviation of the values in list.
This is the square root of the population variance. See VARP for the equation used.
TREND(LINEST_data,x_data)
Takes the result of the LINEST function (LINEST_data) and applies it to x_data to return estimated y_data.
VAR(list)
Returns the variance of the values in list.
The equation used is:
(n·(Σ(x2)) - (Σx)2) ÷ n·(n-1)
where x is each value and n is the number of values.
VARP(list)
Returns the population variance of the values in list.
The equation used is:
(n·(Σ(x2)) - (Σx)2) ÷ n2
where x is each value and n is the number of values.