Statistical Commands

LARGE
RANK
SMALL
SUM
UNIQUE RANK


LARGE

Finds the nth largest val­ue in the cell group

Format: LARGE ( ref­er­ence .. ref­er­ence RANKED ranking )

Arguments: ref­er­ence: col­umn num­ber or col­umn title, row num­ber or quot­ed text rep­re­sent­ing a cell title rank­ing: a num­ber rep­re­sent­ing the nth largest val­ue. 1 being the largest

Available: Available for spread­sheet win­dows Version: 9 and later

Example:

large (2,1..4,3 ranked 2)
Returns the 2nd largest val­ue of all cells between col­umn 2, row 1 and col­umn 4, row 3

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

large (2,1..“column1”,4 ranked 3 )
Returns the 3rd largest val­ue of all cells between col­umn 2, row 1 and the col­umn with the title column1” on row 4 

large ($column+1,$row..$column+2,$row+1 ranked 1)
Returns the largest val­ue of all cells 1 col­umn right of cur­rent cell to 2 columns right, 1 row down


RANK

Finds the rank of the val­ue in the range

Format: RANK ( val­ue IN reference..reference order)

Arguments: ref­er­ence: col­umn num­ber or col­umn title, row num­ber val­ue: a num­ber to search reference..reference for order: LARGEST or SMALLEST depend­ing on what you want the rank to rep­re­sent. The val­ue is option­al and will default to LARGEST

Available: Available for spread­sheet win­dows Version: 9 and later

Example:

rank (2 in 2,1..4,3 largest)
Returns the 2nd largest val­ue of all cells between col­umn 2, row 1 and col­umn 4, row 3 

rank ( 1, column1”,1..“column1”,5 )
Returns the largest val­ue from all cells between row 1 and 5 in col­umn column1”

rank ( 2, column1”,4..“column2”,9 small­est )
Returns the 2nd small­est val­ue of all cells between column1”, row 4 and column2”, row 9


SMALL

Finds the nth small­est val­ue in the cell group

Format: SMALL ( ref­er­ence .. ref­er­ence RANKED ranking )

Arguments: ref­er­ence: col­umn num­ber or col­umn title, row num­ber or quot­ed text rep­re­sent­ing a cell title rank­ing: a num­ber rep­re­sent­ing the nth small­est val­ue. 1 being the smallest

Available: Available for spread­sheet win­dows Version: 9 and later

Example:

small (2,1..4,3 ranked 2)
Returns the 2nd small­est val­ue of all cells between col­umn 2, row 1 and col­umn 4, row 3 

small (“cell1”..“cell2” ranked 1 )
Returns the small­est val­ue from all cells between cells with titles cell1” and cell2”

small (2,1..“column1”,4 ranked 3 )
Returns the 3rd small­est val­ue of all cells between col­umn 2, row 1 and the col­umn with the title column1” on row 4

small ($column+1,$row..$column+2,$row+1 ranked 1)
Returns the small­est val­ue of all cells 1 col­umn right of cur­rent cell to 2 columns right, 1 row down


SUM

adds all the val­ues in the cell group

Format: SUM ( ref­er­ence .. reference )

Arguments: ref­er­ence: col­umn num­ber or col­umn title, row num­ber or quot­ed text rep­re­sent­ing a cell title

Available: Available for spread­sheet win­dows Version: 9 and later

Example:

sum (2,1..4,3)
Returns the sum of all cells between col­umn 2, row 1 and col­umn 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 col­umn 2, row 1 and the col­umn with the title column1” on row 4

sum ($column+1,$row..$column+2,$row+1)
Returns the sum of all cells 1 col­umn right of cur­rent cell to 2 columns right, 1 row down


UNIQUE RANK

Finds the rank of the val­ue in the range mak­ing sure it is unique

Format: UNIQUE RANK ( val­ue IN reference..reference UNIQUE reference..reference order)

Arguments: ref­er­ence: col­umn num­ber or col­umn title, row num­ber val­ue: a num­ber to search reference..reference for order: LARGEST or SMALLEST depend­ing on what you want the rank to rep­re­sent. The val­ue is option­al and will default to LARGEST

Available: Available for spread­sheet win­dows 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 posi­tion” col­umn between rows 2 and 9 will pro­duce a lad­der posi­tion based on points. It works because it does a nor­mal RANK com­mand and then checks the val­ues above it. If it finds there is anoth­er team on equal points it will return the next num­ber not used in the unique range. The cell in row 1 of the posi­tion” col­umn would only need the RANK command