This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi, so I'm trying to generate an average based on many rows with the same date but at different times of the day. I need the formula to look at my date column on one table and then go to the source data table, look in the date column and take an average of all the values against that date. I know this is achievable but I can't figure it out! Please see versions of the tables below. Any ideas greatly appreciated!
Example of base data set
| Date | Date/Time | Yellow | Green | Blue |
|---|---|---|---|---|
| 01/01/24 | 01/01/24 08:00 | 1 | 1 | 0 |
| 01/01/24 | 01/01/24 12:00 | 0 | 1 | 0 |
| 01/01/24 | 01/01/24 16:00 | 1 | 1 | 1 |
| 01/01/24 | 01/01/24 20:00 | 1 | 1 | 1 |
| 02/01/24 | 02/01/24 08:00 | 1 | 0 | 1 |
| 02/01/24 | 02/01/24 12:00 | 1 | 0 | 1 |
| 02/01/24 | 02/01/24 16:00 | 1 | 0 | 0 |
| 02/01/24 | 02/01/24 20:00 | 1 | 0 | 1 |
I want the results to look like this:
| Date | Yellow | Green | Blue |
|---|---|---|---|
| 01/01/24 | 75% | 100% | 50% |
| --- | --- | --- | --- |
| 02/01/24 | 100% | 0% | 75% |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
With data in A1:E9, and unique dates in A13:A14:
.
The formula in B13 is
=AVERAGEIFS(C$2:C$9,$A$2:$A$9,$A13)