 # Gathering two maximum and two minimum values of a data set

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?

6 Replies

# Re: Gathering two maximum and two minimum values of a data set

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

# Re: Gathering two maximum and two minimum values of a data set

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

# Re: Gathering two maximum and two minimum values of a data set

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.

# Re: Gathering two maximum and two minimum values of a data set

Data = A1:A24  as an example.

``=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})``````

# Re: Gathering two maximum and two minimum values of a data set

=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. 