Gathering two maximum and two minimum values of a data set

[agover17]

I have a set of data that has an entry every hour of the day, so 24 entries per day. The data set covers roughly six months. I am trying to find the greatest two values and the lowest two values of each day. What's the trick?

[#2]

@agover17
It is possible with conditional formatting for colored cells
and otherwise with

highest 2
=MAX(A1:A100)
and
=MAX(A1:A100)-1
lowest 2
=MIN(A1:A100)
and
=MIN(A1:A100)+1

[#3]

@agover17 

Depends on Excel version and on in which form you'd like to have the result. Perhaps the easiest is to create PivotTable.

[#4]

@agover17 

Oops, I missed you need 4 values for each date. When PivotTable possible, but perhaps not easiest variant.

If with formulae depends on your Excel version.

[#5]

@agover17 

Data = A1:A24  as an example.

If you have access to stacking functions perhaps:

=VSTACK(LARGE(data,{1,2}),SMALL(data,{1,2}))

Otherwise, you could split SMALL and LARGE into two formulas:

top 2
=LARGE(data,{1,2})
bottom 2
=SMALL(data,{1,2})

[#6]

@agover17 

=LET(range,A2:Y182,VSTACK({"date "."Max 1"."Max 2"."Min 1"."Min 2"},HSTACK(TAKE(range,,1),BYROW(range,LAMBDA(row,LARGE(DROP(row,,1),1))),BYROW(range,LAMBDA(row,LARGE(DROP(row,,1),2))),BYROW(range,LAMBDA(row,SMALL(DROP(row,,1),1))),BYROW(range,LAMBDA(row,SMALL(DROP(row,,1),2))))))

An alternative could be this formula which spills the results as shown in the screenshot. If you add dates and numbers you only have to adapt the range to dynamically update the result.

[#7]

@OliverScheurich Thank you so much!