Forum Discussion
Sanjyn81
Dec 09, 2023Copper Contributor
Autofill large increment
Hello
I have two questions regarding the same spreadsheet. Let me make a small example first:
A B C D E
01-01-2021 00:00 1.23 01-01-2021
01-01-2021 01:00 1.03 02-01-2021
01-01-2021 02:00 1.15 03-01-2021
01-01-2021 03:00 1.47 04-01-2021
01-01-2021 04:00 1.68 05-01-2021
.
.
31-12-2021 23:00
1) In column D I would like to display the MAX value for each day, which is simple enough, "=MAX(B2:B25)" and the next would be "=MAX(B26:49)" and so on. Is it possible to autofill for the entire year?? If I use the usual autofill it only increment by 1 and not 23, which is the idea.
2) Is it possible, in column E, to display the corresponding max value from column D to the value in column A?? In the above example the max value from the day would be "1.68" and then the value in E2 would be "01-01-2021 04:00".
Thanks
- LouisDeconinckBrass Contributor
Sanjyn81 First we create a new column (C) with the date of the date-time column, with this formula:
=DATE(YEAR(A1),MONTH(A1),DAY(A1))
Then we find the maximum using this formula:
=MAXIFS($B$1:$B$72,$C$1:$C$72,E1)
Then we can find the associated time where this maximum occurred using the following formula:
=XLOOKUP(F1&E1,$B$1:$B$72&$C$1:$C$72,$A$1:$A$72)
I made a custom video explaining how to apply these formulas in your case: https://www.youtube.com/watch?v=A14HPBmHcp4
Optional tip if this was helpful: [link removed by admin]
- Sanjyn81Copper ContributorThis worked super great, thanks.
- OliverScheurichGold Contributor
An alternative suggestion for e.g. Excel 2013 is in the attached file.