SYNTAX:

STATZ(Cell Range,”Stat”, Cascade Rule, MDL Rule, Dec Places, optional "No_Value_Print")

Cell Range: The cell range to anaylze. i.e. (A1:A31)

Stat:  The Statistic to calculate (must be in quotes)

• AVG  Average
• MIN  Minimum
• MAX  Maximum
• SUM  Total
• NOS  Number of values (ie count)
• FIRST  Finds the first value in the cell range
• LAST  Finds the last value in the cell range
• AVG_MED Returns the average if all values do not contain a data qualifier, otherwise returns the median.  See  Average and Geometric Mean reporting for California DMR 
• GEOMEAN_MED Returns the Geometric Mean if all values do not contain a data qualifier, otherwise returns the median.  See  Average and Geometric Mean reporting for California DMR 
  

Cascade Rule: Sets when to display the data qualifiers (<,>, ND) with the calculated statistic.  Valid settings are:

0 - Display no data qualifiers in result.
1 - Display the data qualifier if one of the values contains the qualifier.
2 - Display data qualifier if over half the values contains the qualifier.
3 - Display data qualifier if all of the values contains the qualifier.
4 - Cascade all data qualifiers.

MDL Rule: Sets what value to use the in calculation of the statistic when cells contain data qualifiers.

0 - Zero
1 - Entered Value
2 - Half of <, twice of >
3 - Zero if <, Entered Value if >

Decimal Places: Sets the number of decimal places to be displayed.

No_Value_Print: OPTIONAL.  Sets a string to be printed if there are no values in the date range.

 

EXAMPLES:

STATZ(B$3:B$5,"SUM",0,1,0)

STATZ(B3,B7,"AVG_MED",0,1,1) - takes the "Average Median" for California DMR reporting.  Click here for a examples of AVG_MED calculations.