You can use the following formula to calculate the average value by date in an Excel spreadsheet:
=AVERAGEIF(A1:A10, C1, B1:B10)
This particular formula calculates the average value in the cell range B1:B10 only where the corresponding cells in the range A1:A10 are equal to the date in cell C1.
The following example shows how to use this formula in practice.
Example: Calculate Average by Date in Excel
Suppose we have the following dataset that shows the sales of some product on various dates:
Now suppose we’d like to calculate the average sales value by date.
To generate a list of unique dates, we can use the following formula:
=SORT(UNIQUE(A2:A15))We’ll type this formula into cell D2:
By default, Excel converts the date to the number of days since 1/1/1900.
To convert these numbers to a recognizable date format, simply highlight the cell range D2:D6, then click the format dropdown arrow and click Short Date:
The dates will be converted to a recognizable date format:
=AVERAGEIF($A$2:$A$15, D2, $B$2:$B$15)
We’ll type this formula into cell E2, then copy and paste it into each remaining cell in column E:
From the output we can see:
- The average sales on 1/4/2022 is 5.
- The average sales on 1/7/2022 is 6.333.
- The average sales on 2/7/2022 is 6.333.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: