AVERAGEIF function
PRO
BASIC
The AVERAGEIF
function in Jspreadsheet Formulas Pro is a tool that allows you to calculate the average of a group of cells within a specific range that meet a certain condition. For instance, you could use this function to find the average sales volume for a particular product, or the average score of a student in tests. The formula takes into account only those cells that fulfill the set criteria, before calculating the average. This makes it a powerful function for analyzing specific data within a larger dataset.
Documentation
Returns the average (arithmetic mean) of all cells in a range that meet a specified criteria.
Category
Statistical
Syntax
AVERAGEIF(range, criteria, [average_range])
Parameter | Description |
---|---|
range |
The range of cells to be evaluated by the criteria. |
criteria |
The criteria used to determine which cells to include in the average. Can be a number, expression, cell reference, or text string. |
average_range |
Optional. The range of cells to be averaged. If omitted, the range parameter is also used as the average range. |
Behavior
The AVERAGEIF
function calculates the average of the cells specified by a given condition or criteria. Here's how it handles different contents:
-
Empty cells: Empty cells are ignored during the calculation.
-
Text: The function does not consider text when calculating the average. However, text can be used as a condition or criteria for the cells to be averaged.
-
Booleans: Boolean values are treated as numbers, with TRUE as 1 and FALSE as 0.
-
Errors: If any cell in the range has an error, the function will return an error.
-
Non-number criteria: Non-number criteria are enclosed in double-quotes. For example, text or dates should be given in double-quotes.
Common Errors
Error | Description |
---|---|
#DIV/0! |
This error occurs if the criteria does not match any cell in the range, causing a division by zero. |
#VALUE! |
This error occurs when the criteria is a text string that is not enclosed in double quotes. |
Best practices
Always ensure your criteria is in the correct format. If you're using a text string or a date as the criteria, make sure it is enclosed in double quotes.
Use absolute cell references if you want to copy your
AVERAGEIF
formula to other cells without changing the range.Avoid including error cells in your range to prevent the
AVERAGEIF
function from returning an error.Handle
#DIV/0!
errors by wrapping yourAVERAGEIF
function with theIFERROR
function. This way you can display a friendlier message or a zero instead of the error.
Usage
A few examples using the AVERAGEIF function.
AVERAGEIF(A1:A5, '>3')
AVERAGEIF(B2:F2, 'red', B3:F5)
AVERAGEIF(C2:C9, '<>0', D2:D9)
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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Product",
"Category",
"Price",
"Sales"
],
[
"Laptop",
"Electronics",
899,
45
],
[
"Phone",
"Electronics",
599,
32
],
[
"Desk",
"Furniture",
299,
18
],
[
"Chair",
"Furniture",
159,
25
],
[
"Tablet",
"Electronics",
399,
28
],
[
"",
"",
"Avg Electronics Price:",
"=AVERAGEIF(B2:B6,\"Electronics\",C2:C6)"
],
[
"",
"",
"Avg High Sales:",
"=AVERAGEIF(D2:D6,\">30\",C2:C6)"
]
]
}]
});
</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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Product",
"Category",
"Price",
"Sales"
],
[
"Laptop",
"Electronics",
899,
45
],
[
"Phone",
"Electronics",
599,
32
],
[
"Desk",
"Furniture",
299,
18
],
[
"Chair",
"Furniture",
159,
25
],
[
"Tablet",
"Electronics",
399,
28
],
[
"",
"",
"Avg Electronics Price:",
"=AVERAGEIF(B2:B6,\"Electronics\",C2:C6)"
],
[
"",
"",
"Avg High Sales:",
"=AVERAGEIF(D2:D6,\">30\",C2:C6)"
]
];
// 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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Product",
"Category",
"Price",
"Sales"
],
[
"Laptop",
"Electronics",
899,
45
],
[
"Phone",
"Electronics",
599,
32
],
[
"Desk",
"Furniture",
299,
18
],
[
"Chair",
"Furniture",
159,
25
],
[
"Tablet",
"Electronics",
399,
28
],
[
"",
"",
"Avg Electronics Price:",
"=AVERAGEIF(B2:B6,\"Electronics\",C2:C6)"
],
[
"",
"",
"Avg High Sales:",
"=AVERAGEIF(D2:D6,\">30\",C2:C6)"
]
]
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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Product",
"Category",
"Price",
"Sales"
],
[
"Laptop",
"Electronics",
899,
45
],
[
"Phone",
"Electronics",
599,
32
],
[
"Desk",
"Furniture",
299,
18
],
[
"Chair",
"Furniture",
159,
25
],
[
"Tablet",
"Electronics",
399,
28
],
[
"",
"",
"Avg Electronics Price:",
"=AVERAGEIF(B2:B6,\"Electronics\",C2:C6)"
],
[
"",
"",
"Avg High Sales:",
"=AVERAGEIF(D2:D6,\">30\",C2:C6)"
]
]
}]
});
}
}