RANK_AVG

Returns the rank of a number in a list of numbers: its size relative to other values in the list. If more than one value has the same rank, the average rank is returned.

Syntax

expression.RANK_AVG(arg1, arg2, arg3);

expression - A variable that represents a ApiWorksheetFunction class.

Parameters

Name	Required/Optional	Data type	Default	Description
arg1	Required	ApiRange | ApiName | number		The number for which the rank will be returned.
arg2	Required	ApiRange | ApiName | number[]		An array or range of numbers. Nonnumeric values are ignored.
arg3	Optional	ApiRange | ApiName | boolean		The numeric value that specifyes how to order the numbers. If it is 0 or omitted, the rank in the list will be sorted in descending order. Any other numeric value means that the rank in the list will be sorted in ascending order.

Returns

number

Example

Find the rank of a value in a list, using the average rank if there are ties in a spreadsheet.

// How do I rank a number when multiple values are equal in a spreadsheet?

// Determine a value's position in a list with tied items split evenly in rank in a spreadsheet.

const worksheet = Api.GetActiveSheet();

let valueArr = [7, 6, 5, 5];

// Place the numbers in cells
for (let i = 0; i < valueArr.length; i++) {
  worksheet.GetRange("A" + (i + 1)).SetValue(valueArr[i]);
}

//method params
let number = worksheet.GetRange("A3");
let range = worksheet.GetRange("A1:A4");
let order = 0;

let func = Api.WorksheetFunction;
let ans = func.RANK_AVG(number, range, order);

worksheet.GetRange("C1").SetValue(ans);