Revision as of 01:29, 12 January 2008

This page shows how selected database functions in Excel map to Analytica equivalents.

DAVERAGE(table,field,criteria)

Analytica equivalent:

Mean( table[FieldIndex=field], RowIndex, w:criteria )

Analytica equivalents:

Sum( if criteria and IsNumber(table[FieldIndex=field]) else 0, RowIndex )
Sum( criteria, RowIndex )

where criteria is a boolean comparison that evaluates to 0 or 1.

Analytica equivalent:

table[FieldIndex=field,RowIndex=SubIndex(criteria,1,RowIndex)]

Analytica equivalent:

CondMax(table[fieldIndex=field],criteria,RowIndex)

Analytica equivalent:

CondMin(table[fieldIndex=field],criteria,RowIndex)

Analytica equivalent:

Product( If criteria Then table[FieldIndex=field] Else 1, RowIndex )

Analytica equivalent:

SDeviation( table[FieldIndex=field], RowIndex, w:criteria )

Analytica equivalent:

Sum( If criteria Then table[FieldIndex=field] Else 0, RowIndex )

Analytica equivalent:

Variance( table[FieldIndex=field], RowIndex, w:criteria )

@@ Line 1: / Line 1: @@
 This page shows how selected database functions in Excel map to Analytica equivalents.
-= DAVERAGE =
+= DAVERAGE(table,field'',criteria'') =
-= DCOUNT =
-= DCOUNTA =
+Analytica equivalent:
-= DGET =
+ [[Mean]]( table[FieldIndex=field], RowIndex'', w:criteria'' )
-= DMAX =
+= DCOUNT(table,field,criteria) =
+Analytica equivalents:
+ [[Sum]]( if criteria and IsNumber(table[FieldIndex=field]) else 0, RowIndex )
+ [[Sum]]( criteria, RowIndex )
+where criteria is a boolean comparison that evaluates to 0 or 1.
+= DGET(table,field,criteria) =
+Analytica equivalent:
+ table[FieldIndex=field,RowIndex=[[SubIndex]](criteria,1,RowIndex)]
+= DMAX(table,field,criteria) =
+Analytica equivalent:
+ [[CondMax]](table[fieldIndex=field],criteria,RowIndex)
 = DMIN =
-= DPRODUCT =
+Analytica equivalent:
-= DSTDEV =
+ [[CondMin]](table[fieldIndex=field],criteria,RowIndex)
-= DSTDEVP =
-= DSUM =
+= DPRODUCT(table,field,criteria) =
+Analytica equivalent:
+ [[Product]]( If criteria Then table[FieldIndex=field] Else 1, RowIndex )
+= DSTDEV(table,field,criteria) =
+Analytica equivalent:
+ [[SDeviation]]( table[FieldIndex=field], RowIndex, w:criteria )
+= DSUM(table,field,criteria) =
+Analytica equivalent:
+ [[Sum]]( If criteria Then table[FieldIndex=field] Else 0, RowIndex )
 = DVAR =
-= DVARP =
+Analytica equivalent:
+ [[Variance]]( table[FieldIndex=field], RowIndex, w:criteria )