Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel ROW Function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel ROWS Function
  • How to use Excel COLUMN Function
  • Multi-criteria lookup and transpose in Excel

Data Analysis Examples

  • Create Scatter Chart in Excel
  • Subtotal function in Excel
  • Everything about Charts in Excel
  • Conditional Formatting Rules in Excel
  • How To Filter Data in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation must not contain
  • Excel Data validation must contain specific text
  • Excel Data validation date in next 30 days
  • Excel Data validation must begin with

Average response time per month in Excel

by

This tutorial shows how to work Average response time per month in Excel using the example below;

Formula

=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))

Explanation

To average response times by month, you can use a formula based on the AVERAGEIFS function, together with the EOMONTH function.

In the example shown, the formula in G5 is:

=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))

How this formula works

This formula uses the named ranges “dates” (B5:B25) and “durations” (D5:D25). Durations column D are in minutes, calculated by subtracting the date opened from date closed.

Worked Example:   How to calculate working days left in month in Excel

The AVERAGEIFS function is designed to average ranges based on multiple criteria. In this case, we configure AVERAGEIFS to average durations by month using two criteria: (1) matching dates greater than or equal to the first day of the month, (2) matching dates less than or equal to the last day of the month.

To bracket dates by month, we use a simple trick to make things easier: In column F, instead of typing month names (“Jan”, “Feb”, Mar”, etc.) we add we add actual dates for the first of each month (1/1/2016, 2/1/2016, 3/1/2016, etc.),. Then, we use the custom date format (“mmm”) to display the month names.

Worked Example:   Get last weekday in month in Excel

This makes it a lot easier to build the criteria we need for AVERAGEIFS using values in column F.  To match dates greater than or equal to the first of the month, we use:

">="&F5

To match dates less than or equal to the last day of the month, we use:

"<="&EOMONTH(F5,0)

We get the EOMONTH to return the last day of the same month by supplying zero for the monthsargument.

Worked Example:   Average by month in Excel

Note: concatenation with an ampersand (&) is necessary when building criteria based on a cell reference.

Post navigation

Previous Post:

Average numbers ignore zero in Excel

Next Post:

Average the last 3 numeric values in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Join first and last name — Manipulating NAMES in Excel
  • Count Number of Words in Excel
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to count line breaks in cell in Excel worksheet
  • Excel Date & Time Functions Example
  • Get work hours between dates and times in Excel
  • How to join date and text together in Excel
  • Convert Excel time to Unix time in Excel
  • How to get number of days, weeks, months or years between two dates in Excel
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan interest in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles