Database functions are variants of the functions AVG, COUNT, MAX, MIN, STD, STDP, SUM, VAR and VARP.
They operate upon a range of slots (your database) subject to the condition which forms their second parameter. Note that there is an example of each database function in the PipeDream Examples archive.
Database functions all have the form:
Dfunction(range,condition)
For example, with:
A | B | |
---|---|---|
2 | Jane | 1.00 |
3 | Fred | 3.00 |
4 | Alan | 10.00 |
DMAX(B2B4,B2B4<10)
finds the maximum value in the range B2B4 which is less than 10, ie 3.00.
These functions can also be used to manipulate text strings. Use double quotation marks to enclose text strings.
For example:
DMAX(B1B3,A1A3>="b")
finds the maximum value in the range B1B3 for which the corresponding value in the range A1A3 starts with a letter greater than a. The answer here is 3.00 since this is the greatest value in column B whose corresponding value in column A starts with a letter greater than a.
When scanning the range, PipeDream steps through each corresponding range in the condition. In this example, A1 is the first slot examined when considering B1, A2 for B2, etc. Any slot references in the condition are unaffected.
Text strings must be within double quotation marks.
You can use the wildcards ^?
(any single character) and
^#
(any multiple character) in the second half of the
condition.
For example, with:
A | B | C | |
---|---|---|---|
1 | Jane | Brighton | 1.00 |
2 | Fred | Blackpool | 3.00 |
3 | Alan | Coventry | 10.00 |
4 | Mary | Brighton | 4.00 |
5 | Susan | Coventry | 15.00 |
DCOUNTA(B1B5,B1B5="B^#")
returns 3 which is the number of items in the range B1B5 which begin with 'B'.
The database functions are as follows:
DAVG(range,condition)
Returns the average of values in the slots in the range for which the condition is satisfied.
DCOUNT(range,condition)
Returns the number of number slots in the range for which the condition is satisfied.
Note that this operates on number slots only.
As DCOUNT, but applies to all slots, not just number slots.
Usually DCOUNTA should be used in preference to DCOUNT.
DMAX(range,condition)
Returns the maximum value in the range for which the condition is satisfied.
DMIN(range,condition)
Returns the minimum value in the range for which the condition is satisfied.
DSTD(range,condition)
Returns the standard deviation in the range for which the condition is satisfied.
This is the square root of the variance. See DVAR for the equation used.
DSTDP(range,condition)
Returns the population standard deviation in the range for which the condition is satisfied.
This is the square root of the population variance. See DVARP for the equation used.
DSUM(range,condition)
Returns the sum of the values in the range for which the condition is satisfied.
DVAR(range,condition)
Returns the variance of the values in the range for which the condition is satisfied.
The equation used is:
(n·(Σ(x2)) - (Σx)2) ÷ n·(n-1)
where x is each value and n is the number of values.
DVARP(range,condition)
Returns the population variance of the values in the range for which the condition is satisfied.
The equation used is:
(n·(Σ(x2)) - (Σx)2) ÷ n2
where x is each value and n is the number of values.
For information on | See |
---|---|
Other functions which operate upon a range of slots to produce statistics | Statistical functions |