# Autofill large increment

Copper 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

4 Replies

# Re: Autofill large increment

@Sanjyn81 You can try MAXIFS() with date+time comparison.

``=MAXIFS(B:B,A:A,">="&C1+TIME(0,0,0),A:A,"<="&C1+TIME(23,59,59))``

Then try FILTER(), and SORT().

``=@SORT(FILTER(A:B,(A:A>=C1+TIME(0,0,0))*(A:A<=C1+TIME(23,59,59))),2,-1)``

# Re: Autofill large increment

An alternative suggestion for e.g. Excel 2013 is in the attached file.

# Re: Autofill large increment

@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]

# Re: Autofill large increment

This worked super great, thanks.