Extract min/max values from a range of cells which share the same day

pete1229

New Member
Joined
Nov 6, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I have 3 columns: "A" contains the day, "B" contains the hours, and "L" contains some values. For each day, there are 24 hours and these are associated with either negative or positive values. I would like to extract the minimum and maximum value of only the positive values (i.e. >=0) from column "L" for each day and paste them to another column as shown in the image. There are 365 days so 8760 hours and values. What is the best method to achieve this?


image.png
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff v2.xlsm
ABLMNOPQ
111MinMax
2125912092
3134821497
414-69
5150
61620
717-51
81859
91983
1011087
11111-14
1211292
1311387
1411477
15115-86
1611637
17117-29
1811891
1911923
20120-51
21121-22
22122-79
23123-44
24124-61
2521-5
262227
2723-58
2824-5
292577
3026-92
3127-2
3228-74
332997
3421079
35211-97
3621279
37213-85
38214-13
3921580
4021615
4121723
4221891
43219-97
44220-75
4522133
4622292
4722350
4822486
4922414
Main
Cell Formulas
RangeFormula
P2:P3P2=MINIFS(L:L,A:A,O2,L:L,">0")
Q2:Q3Q2=MAXIFS(L:L,A:A,O2)
 
Solution

pete1229

New Member
Joined
Nov 6, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
You're a genius! Thank you very much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,661
Messages
5,626,155
Members
416,164
Latest member
hamburger138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top