RANK.AVG function
PRO
BASIC
The RANK.AVG
function in Jspreadsheet Formulas Pro is a tool that helps you determine the position of a specific value within a dataset. If two or more values are the same, it gives them an average rank, instead of identical ranks. The ranking is done based on the order of numbers, where the highest value is ranked number 1. This function is particularly handy when you want to compare the significance of different numbers in a dataset.
Documentation
Returns the rank of a specified value in a dataset, with ties receiving an average rank. The returned rank is based on the order of values in the array or range, with the largest value receiving a rank of 1.
Category
Statistical
Syntax
RANK.AVG(number, ref, [order])
Parameter | Description |
---|---|
number |
The value for which to find the rank. |
ref |
An array or range of cells containing the values to rank. |
order |
Optional. A numeric value specifying how to rank if two or more values are identical. 0 or omitted ranks in descending order (largest to smallest), any other value ranks in ascending order (smallest to largest). |
Behavior
The RANK.AVG
function ranks the specified number within a set of numbers. If there are duplicate numbers in the list, it will return the average rank.
- Empty cells: If a range or array argument contains empty cells, those cells are ignored in calculations.
- Text: If cells contain text, those values are also ignored.
- Booleans: Boolean values are treated as numbers (TRUE = 1, FALSE = 0).
- Errors: If the specified number is not found in the list,
RANK.AVG
will return an error.
Common Errors
Error | Description |
---|---|
#VALUE! | Occurs if the supplied number is non-numeric. |
#N/A | Occurs if the specified number is not found within the supplied array. |
Best practices
- Always ensure that the number you want to rank is included in the array or range of numbers.
- The
RANK.AVG
function will ignore text and cannot rank boolean or string values. Ensure your data set only includes numeric values to avoid errors.- If you are working with a large data set, consider using the
RANK.AVG
function in combination with other functions for better data analysis and accuracy.- Remember that the
RANK.AVG
function returns the average rank if there are duplicate values in the array. If you want to rank without averaging, consider using theRANK.EQ
function instead.
Usage
A few examples using the RANK.AVG function.
RANK.AVG(B2, A2:A10)
// Returns the rank of the value in B2 among A2:A10, with ties averaged
RANK.AVG(100, B2:B10, 0)
// Returns the rank of 100 in B2:B10 (descending order).
// If multiple 100s exist, they share the average rank.
RANK.AVG(C2, $D$2:$D$10, 1)
// Returns the rank of C2 within D2:D10 in ascending order (smallest = 1)
Interactive Spreadsheet Demo
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<div id="spreadsheet"></div>
<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('N2UyOWNmMzQ3NzM2NmRlZTk0NDEzM2NiMmU3NWIwZDhlNmIyY2NjMTFlOTY2YjFkMzA1MjhkODUyYzY2NjY1ZjZhMWRhYjdmZjllZDc0MDNhYmVmMWU2MGE0OWE2YjgyNDg0ZjEyODU5NWY4Mjk2OTYzMmNhNDI1YWE2ZjBlZTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNakkyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Student",
"Score",
"Rank"
],
[
"Alice",
85,
"=RANK.AVG(B2,B$2:B$6)"
],
[
"Bob",
92,
"=RANK.AVG(B3,B$2:B$6)"
],
[
"Carol",
78,
"=RANK.AVG(B4,B$2:B$6)"
],
[
"David",
92,
"=RANK.AVG(B5,B$2:B$6)"
],
[
"Eve",
88,
"=RANK.AVG(B6,B$2:B$6)"
]
]
}]
});
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
// Set license
jspreadsheet.setLicense('N2UyOWNmMzQ3NzM2NmRlZTk0NDEzM2NiMmU3NWIwZDhlNmIyY2NjMTFlOTY2YjFkMzA1MjhkODUyYzY2NjY1ZjZhMWRhYjdmZjllZDc0MDNhYmVmMWU2MGE0OWE2YjgyNDg0ZjEyODU5NWY4Mjk2OTYzMmNhNDI1YWE2ZjBlZTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNakkyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Student",
"Score",
"Rank"
],
[
"Alice",
85,
"=RANK.AVG(B2,B$2:B$6)"
],
[
"Bob",
92,
"=RANK.AVG(B3,B$2:B$6)"
],
[
"Carol",
78,
"=RANK.AVG(B4,B$2:B$6)"
],
[
"David",
92,
"=RANK.AVG(B5,B$2:B$6)"
],
[
"Eve",
88,
"=RANK.AVG(B6,B$2:B$6)"
]
];
// Render component
return (
<Spreadsheet ref={spreadsheet}>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet">
<Worksheet :data="data" />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";
// Set license
jspreadsheet.setLicense('N2UyOWNmMzQ3NzM2NmRlZTk0NDEzM2NiMmU3NWIwZDhlNmIyY2NjMTFlOTY2YjFkMzA1MjhkODUyYzY2NjY1ZjZhMWRhYjdmZjllZDc0MDNhYmVmMWU2MGE0OWE2YjgyNDg0ZjEyODU5NWY4Mjk2OTYzMmNhNDI1YWE2ZjBlZTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNakkyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Student",
"Score",
"Rank"
],
[
"Alice",
85,
"=RANK.AVG(B2,B$2:B$6)"
],
[
"Bob",
92,
"=RANK.AVG(B3,B$2:B$6)"
],
[
"Carol",
78,
"=RANK.AVG(B4,B$2:B$6)"
],
[
"David",
92,
"=RANK.AVG(B5,B$2:B$6)"
],
[
"Eve",
88,
"=RANK.AVG(B6,B$2:B$6)"
]
]
return {
data
};
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('N2UyOWNmMzQ3NzM2NmRlZTk0NDEzM2NiMmU3NWIwZDhlNmIyY2NjMTFlOTY2YjFkMzA1MjhkODUyYzY2NjY1ZjZhMWRhYjdmZjllZDc0MDNhYmVmMWU2MGE0OWE2YjgyNDg0ZjEyODU5NWY4Mjk2OTYzMmNhNDI1YWE2ZjBlZTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNakkyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngAfterViewInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [{
data: [
[
"Student",
"Score",
"Rank"
],
[
"Alice",
85,
"=RANK.AVG(B2,B$2:B$6)"
],
[
"Bob",
92,
"=RANK.AVG(B3,B$2:B$6)"
],
[
"Carol",
78,
"=RANK.AVG(B4,B$2:B$6)"
],
[
"David",
92,
"=RANK.AVG(B5,B$2:B$6)"
],
[
"Eve",
88,
"=RANK.AVG(B6,B$2:B$6)"
]
]
}]
});
}
}