Excel to Analytica Mappings/Database Functions

This page shows Analytica expressions that are equivalent to selected Excel database functions. They assume that you are applying each Excel function to a range, named relational table, t.

In Analytica, you apply each function to an Array, t, indexed by Rows and Cols. Index Cols identifies the columns or fields of each record. criteria is a Boolean expression involving t[Cols == field] that evaluates to 0 or 1 for each Row.

DAVERAGE(t, field, criteria)

Analytica equivalent:

Mean(t[Cols == field],  Rows,  w: criteria)

DCOUNT(t, field, criteria)

Analytica equivalent:

Sum(criteria,  Rows)

DGET(t, field, criteria)

Analytica equivalent:

t[Cols == field,  Rows == SubIndex(criteria,  1,  Rows)]

DMAX(t, field, criteria)

Analytica equivalent:

CondMax(t[Cols == field],  criteria,  Rows)

DMIN

Analytica equivalent:

CondMin(t[Cols == field],  criteria,  Rows)

DPRODUCT(t, field, criteria)

Analytica equivalent:

Product(IF criteria THEN t[Cols ==field] Else 1,  Rows)

DSTDEV(t, field, criteria)

Analytica equivalent:

SDeviation(t[Cols ==field],  Rows,  w: criteria)

DSUM(g, field, criteria)

Analytica equivalent:

Sum(If criteria Then t[Cols == field] Else 0,  Rows)

DVAR

Analytica equivalent:

Variance(t[Cols == field],  Rows,  w: criteria )