Database functions

Database functions are variants of the functions AVG, COUNT, MAX, MIN, STD, STDP, SUM, VAR and VARP.

They operate upon a range of cells (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, i.e. 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 cell examined when considering B1, A2 for B2, etc. Any cell 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

DAVG(range, condition)

Returns the average of values in the cells in the range for which the condition is satisfied.

DCOUNT

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

As DCOUNT, but applies to all cells, not just number cells.

Usually DCOUNTA should be used in preference to DCOUNT.

DMAX

DMAX(range, condition)

Returns the maximum value in the range for which the condition is satisfied.

DMIN

DMIN(range, condition)

Returns the minimum value in the range for which the condition is satisfied.

DSTD

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

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

DSUM(range, condition)

Returns the sum of the values in the range for which the condition is satisfied.

DVAR

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

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 cells to produce statistics Statistical functions