Statistical Commands

LARGE
RANK
SMALL
SUM
UNIQUE RANK


LARGE

Finds the nth largest value in the cell group

Format: LARGE ( reference .. reference RANKED ranking )

Arguments: reference: column number or column title, row number or quoted text representing a cell title ranking: a number representing the nth largest value. 1 being the largest

Available: Available for spreadsheet windows Version: 9 and later

Example:

large (2,1..4,3 ranked 2)
Returns the 2nd largest value of all cells between column 2, row 1 and column 4, row 3

large (“cell1”..”cell2” ranked 1 )
Returns the largest value from all cells between cells with titles “cell1” and “cell2”

large (2,1..”column1”,4 ranked 3 )
Returns the 3rd largest value of all cells between column 2, row 1 and the column with the title “column1” on row 4 

large ($column+1,$row..$column+2,$row+1 ranked 1)
Returns the largest value of all cells 1 column right of current cell to 2 columns right, 1 row down


RANK

Finds the rank of the value in the range

Format: RANK ( value IN reference..reference order)

Arguments: reference: column number or column title, row number value: a number to search reference..reference for order: LARGEST or SMALLEST depending on what you want the rank to represent. The value is optional and will default to LARGEST

Available: Available for spreadsheet windows Version: 9 and later

Example:

rank (2 in 2,1..4,3 largest)
Returns the 2nd largest value of all cells between column 2, row 1 and column 4, row 3 

rank ( 1, “column1”,1..”ccolumn1”,5 )
Returns the largest value from all cells between row 1 and 5 in column “column1”

rank ( 2, “column1”,4..”column2”,9 smallest )
Returns the 2nd smallest value of all cells between “column1”, row 4 and “column2”, row 9


SMALL

Finds the nth smallest value in the cell group

Format: SMALL ( reference .. reference RANKED ranking )

Arguments: reference: column number or column title, row number or quoted text representing a cell title ranking: a number representing the nth smallest value. 1 being the smallest

Available: Available for spreadsheet windows Version: 9 and later

Example:

small (2,1..4,3 ranked 2)
Returns the 2nd smallest value of all cells between column 2, row 1 and column 4, row 3 

small (“cell1”..”cell2” ranked 1 )
Returns the smallest value from all cells between cells with titles “cell1” and “cell2”

small (2,1..”column1”,4 ranked 3 )
Returns the 3rd smallest value of all cells between column 2, row 1 and the column with the title “column1” on row 4

small ($column+1,$row..$column+2,$row+1 ranked 1)
Returns the smallest value of all cells 1 column right of current cell to 2 columns right, 1 row down


SUM

adds all the values in the cell group

Format: SUM ( reference .. reference )

Arguments: reference: column number or column title, row number or quoted text representing a cell title

Available: Available for spreadsheet windows Version: 9 and later

Example:

sum (2,1..4,3)
Returns the sum of all cells between column 2, row 1 and column 4, row 3

sum (“cell1”..”cell2”)
Returns the sum of all cells between cells with titles “cell1” and “cell2”

sum (2,1..”column1”,4)
Returns the sum of all cells between column 2, row 1 and the column with the title “column1” on row 4

sum ($column+1,$row..$column+2,$row+1)
Returns the sum of all cells 1 column right of current cell to 2 columns right, 1 row down


UNIQUE RANK

Finds the rank of the value in the range making sure it is unique

Format: UNIQUE RANK ( value IN reference..reference UNIQUE reference..reference order)

Arguments: reference: column number or column title, row number value: a number to search reference..reference for order: LARGEST or SMALLEST depending on what you want the rank to represent. The value is optional and will default to LARGEST

Available: Available for spreadsheet windows Version: 9 and later

Example:

show unique rank( cell”points” in “points”,1..”points”,9 unique “position”,1..”position”,$row-1)
Placing this script in all of the cells in “position” column between rows 2 and 9 will produce a ladder position based on points. It works because it does a normal RANK command and then checks the values above it. If it finds there is another team on equal points it will return the next number not used in the unique range. The cell in row 1 of the “position” column would only need the RANK command