DAVERAGE function

PRO BASIC

The DAVERAGE function in Jspreadsheet Formulas Pro is a handy tool that helps you calculate the average of particular data entries in your database that meet certain criteria. It's a way of narrowing down your data to find an average from a specific subset. For instance, you could use DAVERAGE to find the average sales in a month where the sales were above a certain number. It simplifies the task of sifting through large amounts of data to find precise information.

Documentation

Returns the average of selected database entries based on specified criteria.

Syntax

DAVERAGE(database, field, criteria)

Parameter	Description
`database`	The range of cells that makes up the database, including the headers.
`field`	The column header indicating the field to be averaged.
`criteria`	The range of cells that contains the criteria. Each column in the criteria range should contain a separate criterion and the first row should contain the column headers that match the database headers.

Behavior

The DAVERAGE function in Jspreadsheet is designed to calculate the average of selected database entries based on specified criteria. It operates as follows:

It requires three arguments: database, field, criteria. The database is the range of cells that makes up the list or database. The field indicates which column in the database contains the numbers to be averaged. The criteria are the conditions that the cells need to meet to be included in the average.
It handles empty cells by ignoring them. They are not counted in the average calculation.
If the field argument is a text string, DAVERAGE matches it against the column headers in the database. If it is a number, DAVERAGE uses it as the index position in the list of column headers.
It handles boolean values as numbers: TRUE is 1 and FALSE is 0.
If the criteria argument is omitted, DAVERAGE averages all records in the database.
If there are no records in the database that meet the criteria, DAVERAGE returns a #DIV0! error.

Common Errors

Error	Description
#VALUE!	This error occurs when the field argument is neither a valid data field nor a column label that exists in the database.
#DIV0!	This error is returned when there are no rows in the database that meet the criteria. In other words, `DAVERAGE` is trying to divide by zero, which is not permissible.
#NUM!	This error occurs when the field argument is less than 1 or greater than the number of columns in the database.
#N/A	This error occurs when the criteria argument includes a column label that does not match any column labels in the database.

Best practices

Make sure to correctly define the criteria range, which includes at least one column label and at least one cell below the column label for specifying a condition for the corresponding column.

Be cautious when inputting the field argument. If it's a number, it should correspond to the position of the column in the list; if it's text, it should match exactly with the column label in the database.

Use absolute cell references for the database and criteria range to ensure the correctness of the function when copying it to other cells.

Double-check your data range to avoid #DIV0! errors. Ensure there are rows that meet your specified criteria.

Usage

A few examples using the DAVERAGE function.

// Example 1: Average of "Sales" where Region is "North"
DAVERAGE(A1:D10, "Sales", F1:F2)

// Example 2: Average of "Units Sold" where Product is "Widget" and Region is "East"
DAVERAGE(A1:E20, "Units Sold", H1:I2)

// Example 3: Using named ranges
DAVERAGE(SalesData, "Revenue", Criteria)

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('OGU3MzczMDk5YmEwNGM5NzYxOTg0OTQ3MjU4NGFkZmZmY2FkYzM4ZDgyODBiYmFiN2Q4YWJkZWNlODZmM2MzYjA5ZTFlYjFhYTUxNjhlODk5NGFhOGFkMGZkYjU5MDkzZWQzNzhkNGFmZTAwOTk4YjE4NmZiZTEwMTI2YTgyNmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Employee",
        "Department",
        "Sales",
        "Years"
    ],
    [
        "John",
        "North",
        45000,
        3
    ],
    [
        "Sarah",
        "South",
        52000,
        5
    ],
    [
        "Mike",
        "North",
        38000,
        2
    ],
    [
        "Lisa",
        "East",
        61000,
        7
    ],
    [
        "Tom",
        "North",
        49000,
        4
    ],
    [],
    [
        "Department",
        "Years"
    ],
    [
        "North",
        ">2"
    ],
    [],
    [
        "Average North Sales (>2 years):",
        "=DAVERAGE(A1:D6,\"Sales\",A8:B9)"
    ]
]
  }]
});
</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('OGU3MzczMDk5YmEwNGM5NzYxOTg0OTQ3MjU4NGFkZmZmY2FkYzM4ZDgyODBiYmFiN2Q4YWJkZWNlODZmM2MzYjA5ZTFlYjFhYTUxNjhlODk5NGFhOGFkMGZkYjU5MDkzZWQzNzhkNGFmZTAwOTk4YjE4NmZiZTEwMTI2YTgyNmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Employee",
        "Department",
        "Sales",
        "Years"
    ],
    [
        "John",
        "North",
        45000,
        3
    ],
    [
        "Sarah",
        "South",
        52000,
        5
    ],
    [
        "Mike",
        "North",
        38000,
        2
    ],
    [
        "Lisa",
        "East",
        61000,
        7
    ],
    [
        "Tom",
        "North",
        49000,
        4
    ],
    [],
    [
        "Department",
        "Years"
    ],
    [
        "North",
        ">2"
    ],
    [],
    [
        "Average North Sales (>2 years):",
        "=DAVERAGE(A1:D6,\"Sales\",A8:B9)"
    ]
];

    // 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('OGU3MzczMDk5YmEwNGM5NzYxOTg0OTQ3MjU4NGFkZmZmY2FkYzM4ZDgyODBiYmFiN2Q4YWJkZWNlODZmM2MzYjA5ZTFlYjFhYTUxNjhlODk5NGFhOGFkMGZkYjU5MDkzZWQzNzhkNGFmZTAwOTk4YjE4NmZiZTEwMTI2YTgyNmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Employee",
        "Department",
        "Sales",
        "Years"
    ],
    [
        "John",
        "North",
        45000,
        3
    ],
    [
        "Sarah",
        "South",
        52000,
        5
    ],
    [
        "Mike",
        "North",
        38000,
        2
    ],
    [
        "Lisa",
        "East",
        61000,
        7
    ],
    [
        "Tom",
        "North",
        49000,
        4
    ],
    [],
    [
        "Department",
        "Years"
    ],
    [
        "North",
        ">2"
    ],
    [],
    [
        "Average North Sales (>2 years):",
        "=DAVERAGE(A1:D6,\"Sales\",A8:B9)"
    ]
]

        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('OGU3MzczMDk5YmEwNGM5NzYxOTg0OTQ3MjU4NGFkZmZmY2FkYzM4ZDgyODBiYmFiN2Q4YWJkZWNlODZmM2MzYjA5ZTFlYjFhYTUxNjhlODk5NGFhOGFkMGZkYjU5MDkzZWQzNzhkNGFmZTAwOTk4YjE4NmZiZTEwMTI2YTgyNmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pORFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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: [
    [
        "Employee",
        "Department",
        "Sales",
        "Years"
    ],
    [
        "John",
        "North",
        45000,
        3
    ],
    [
        "Sarah",
        "South",
        52000,
        5
    ],
    [
        "Mike",
        "North",
        38000,
        2
    ],
    [
        "Lisa",
        "East",
        61000,
        7
    ],
    [
        "Tom",
        "North",
        49000,
        4
    ],
    [],
    [
        "Department",
        "Years"
    ],
    [
        "North",
        ">2"
    ],
    [],
    [
        "Average North Sales (>2 years):",
        "=DAVERAGE(A1:D6,\"Sales\",A8:B9)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub