Easy solution

Guy Boot

New Member
Joined
Apr 24, 2024
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Simple but I can't find it.
First Column are the date of a whole year and that every average 10 min
2nd column are times, every 10 min with exeptions of intermitend times with highest/lowest value of the 3th column
3th colum A hight ( number 0 which belong to the second column.
I made a macro but the problem is that the nummer of cels down, is never te same as some times I have a high or low value at for example 0:23. This makes the counting of a day mith more cels.
Ik have to work with MATCH, IF or a LOOKUP but cant find it for the moment.

Iff someone has a solution.

To make it short, I need daily a printed sheet with only the needed day and not the whole year.
Yes I could do a copy paste manualy to an other sheet, but I would like to have easy way.

24-04-24​
0:00:00​
1.703​
24-04-24​
0:10:00​
1.737​
24-04-24​
0:20:00​
1.768​
24-04-24​
0:30:00​
1.796​
24-04-24​
0:40:00​
1.821​
24-04-24​
0:50:00​
1.842​
24-04-24​
1:00:00​
1.859​
24-04-24​
1:10:00​
1.872​
24-04-24​
1:20:00​
1.882​
24-04-24​
1:30:00​
1.887​
24-04-24​
1:37:00​
1.888​
24-04-24​
1:40:00​
1.887​
24-04-24​
1:50:00​
1.884​
24-04-24​
2:00:00​
1.877​
24-04-24​
2:10:00​
1.865​
24-04-24​
2:20:00​
1.849​
24-04-24​
2:30:00​
1.829​
24-04-24​
2:40:00​
1.806​
24-04-24​
2:50:00​
1.778​
24-04-24​
3:00:00​
1.747​
24-04-24​
3:10:00​
1.713​
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Not sure I understand what you want. And this is one that's much easier in 365, but this might work.
MrExcelPlayground22.xlsx
ABCDEFGHIJK
1DateTimeDataDay of interest4/25/2024
24/23/20240:00:0015.23515aught to work in 2016 excel365 solution
34/23/20240:10:0016.982954/25/202412:00:00 AM16.280724/25/202412:00:00 AM16.28072
44/23/20240:20:0016.085244/25/202412:10:00 AM13.85074/25/202412:10:00 AM13.8507
54/23/20240:30:0015.698064/25/202412:20:00 AM10.813444/25/202412:20:00 AM10.81344
64/23/20240:40:0010.324754/25/202412:30:00 AM17.910664/25/202412:30:00 AM17.91066
74/23/20240:50:0011.131744/25/202412:40:00 AM17.759434/25/202412:40:00 AM17.75943
84/23/20240:57:1217.058144/25/202412:50:00 AM19.54664/25/202412:50:00 AM19.5466
94/23/20241:00:0019.876684/25/20241:00:00 AM15.988984/25/20241:00:00 AM15.98898
104/23/20241:10:0016.886864/25/20241:10:00 AM15.287764/25/20241:10:00 AM15.28776
114/23/20241:20:0016.419774/25/20241:20:00 AM18.808374/25/20241:20:00 AM18.80837
124/23/20241:30:0010.622334/25/20241:30:00 AM14.81994/25/20241:30:00 AM14.8199
134/23/20241:40:0011.07599 
144/24/20241:50:0018.19051 
154/24/20242:00:0010.0017 
164/24/20242:10:0012.435 
174/24/20242:20:0014.60197 
184/24/20242:30:0011.34742 
194/24/20242:40:0012.24405 
204/24/20242:50:0011.56063 
214/24/20243:00:0017.19389 
224/24/20243:10:0010.95406 
234/24/20243:20:0012.52641 
244/24/20243:30:0015.45774 
254/24/20243:40:0015.87955 
264/24/20243:50:0011.99801 
274/25/20240:00:0016.28072 
284/25/20240:10:0013.8507 
294/25/20240:20:0010.81344 
304/25/20240:30:0017.91066 
314/25/20240:40:0017.75943 
324/25/20240:50:0019.5466 
334/25/20241:00:0015.98898 
344/25/20241:10:0015.28776 
354/25/20241:20:0018.80837 
364/25/20241:30:0014.8199 
374/26/20240:00:0018.40338 
384/26/20240:10:0013.10214 
394/26/20240:20:0011.38531 
404/26/20240:30:0018.99855 
414/26/20240:40:0019.48964 
424/26/20240:50:0014.58728 
434/26/20241:00:0011.48974 
444/26/20241:10:0015.19014 
454/26/20241:20:0012.8444
464/26/20241:30:0016.62728
474/26/20241:40:0012.80817
484/26/20241:50:0011.88026
494/26/20242:00:0016.6568
Sheet10
Cell Formulas
RangeFormula
I3:K12I3=FILTER(A2:C49,A2:A49=F1)
E3:G3E3=INDEX(A2:C49,MATCH(F1,A2:A49,0),{1,2,3})
E13:E44,E4:G12E4=IFERROR(IF(INDEX($A$2:$C$49,ROW()-3+MATCH($F$1,$A$2:$A$49,0),1)=$F$1,INDEX($A$2:$C$49,ROW()-3+MATCH($F$1,$A$2:$A$49,0),{1,2,3}),""),"")
Dynamic array formulas.


This is looking for the first record that matches the day you are looking for, and then offsets from that record until it doesn't match the date you are looking for.
 
Upvote 0
Good evening,

The first column E looks ok. BUt what comes in F3 - F4 and G3 - G4?
Thanks for your time.
 
Upvote 0
Thank you very much. Its working perfect...
My next challenge is more complex with 4 ( if ) and 4 ( Then )
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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
Back
Top