Database functions

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)

Example:

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.

Example:

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.

Example:

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

Syntax:

DAVG(range, condition)

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

DCOUNT

Syntax:

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

Syntax:

DCOUNTA(range, condition)

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

Usually DCOUNTA should be used in preference to DCOUNT.

DMAX

Syntax:

DMAX(range, condition)

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

DMIN

Syntax:

DMIN(range, condition)

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

DSTD

Syntax:

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

Syntax:

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

Syntax:

DSUM(range, condition)

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

DVAR

Syntax:

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

Syntax:

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