Database functions are variants of the statistical functions AVG, COUNT, MAX, MIN, STD, STDP, SUM, VAR and VARP which operate upon a range of cells (your database) subject to the condition which forms their second parameter. There is an example of each database function in the PipeDream Examples.
Database functions all have the form:
Dfunction(range, condition)
With data
A | B | |
---|---|---|
2 | Jane | 1.00 |
3 | Fred | 3.00 |
4 | Alan | 10.00 |
the formula
DMAX(B2B4,B2B4<10)
finds the maximum value in the range B2B4 which is less than 10, i.e. 3.00.
These functions can also be used to manipulate text strings. Use double quotation marks to enclose text strings.
DMAX(B2B4,A2A4>="b")
finds the maximum value in the range B2B4 for which the corresponding value in the range A2A4 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 cell examined when considering B1, A2 for B2, etc. Any cell references in the condition are unaffected.
You can use the wildcards
^?
(any single character) and
^#
(any multiple character)
in the second half of the condition.
With data
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 |
the formula
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 the values in the range for which the condition is satisfied.
DCOUNT(range, condition)
Returns the number of number cells in the range for which the condition is satisfied.
Note that this operates on number cells only.
DCOUNTA(range, condition)
As DCOUNT, but applies to all cells, not just number cells.
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 sample standard deviation of the values in the range for which the condition is satisfied.
This is the square root of the sample variance.
The equation used is:
√{ ( n · (Σi=n (xi2)) - (Σi=n xi)2 ) ÷ (n · (n - 1)) }
where xi is each value and n is the number of values satisfying condition.
DSTDP(range, condition)
Returns the population standard deviation of the values in the range for which the condition is satisfied.
This is the square root of the population variance.
The equation used is:
√{ ( n · (Σi=n (xi2)) - (Σi=n xi)2 ) ÷ n2 }
where xi is each value and n is the number of values satisfying condition.
DSUM(range, condition)
Returns the sum of the values in the range for which the condition is satisfied.
DVAR(range, condition)
Returns the sample variance of the values in the range for which the condition is satisfied.
The equation used is:
( n · (Σi=n (xi2)) - (Σi=n xi)2 ) ÷ (n · (n - 1))
where xi is each value and n is the number of values satisfying condition.
DVARP(range, condition)
Returns the population variance of the values in the range for which the condition is satisfied.
The equation used is:
( n · (Σi=n (xi2)) - (Σi=n xi)2 ) ÷ n2
where xi is each value and n is the number of values satisfying condition.
For information on | See |
---|---|
Other functions which operate upon a range of cells to produce statistics | Statistical functions |