Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Gathering two maximum and two minimum values of a data set

Copper Contributor

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

@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

 


 

@agover17 

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

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

@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})

 

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

 

min max.JPG