Statistical Commands
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
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.."column1",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
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
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