Min and Max in sequence

[Daniel_CW]

Good day,

I have two columns of data, 1st is time and the second are values, The time column is from 4.00am to 4.00am the next day increasing by 1hour intervals, the values increase to a max and then decline in a series of highs and lows. I want to calculate the change in value meaning the highest value - lowest value divided by the corresponding change in time. i dont want a simple max and min since the values are increasing and decreasing in a series of low to highs. i hope you can help, below is just an example of how the data set looks.

time	value
4am	2
5am	3
6am	5
7am	7
8am	6
9am	4
10am	2
11am	3
12pm	5

<tbody>
</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

What about you finish the one-day sample and also provide the expected result(s) for that sample with some explanation of how you got the result manually?

 

[Legacy 452786]

Hi,

Could this work?

Date-time	value	Is highest in cycle	Is lowest in cycle	steps highest-to-lowest	Highest-to-lowest difference	Highest-to-lowest hours	Change rate per hour
2019-11-09 04:00 AM	2	FALSE	FALSE	-	-	-	-
2019-11-09 05:00 AM	3	FALSE	FALSE	-	-	-	-
2019-11-09 06:00 AM	5	FALSE	FALSE	-	-	-	-
2019-11-09 07:00 AM	7	TRUE	FALSE	4	5	3	1.67
2019-11-09 08:00 AM	6	FALSE	FALSE	-	-	-	-
2019-11-09 09:00 AM	4	FALSE	FALSE	-	-	-	-
2019-11-09 10:00 AM	2	FALSE	TRUE	-	-	-	-
2019-11-09 11:00 AM	3	FALSE	FALSE	-	-	-	-
2019-11-09 12:00 PM	5	TRUE	FALSE	2	3	1	3.00
2019-11-09 01:00 PM	2	FALSE	TRUE	-	-	-	-
2019-11-09 02:00 PM	3	FALSE	FALSE	-	-	-	-
2019-11-09 03:00 PM	4	FALSE	FALSE	-	-	-	-
2019-11-09 04:00 PM	7	FALSE	FALSE	-	-	-	-
2019-11-09 05:00 PM	9	FALSE	FALSE	-	-	-	-
2019-11-09 06:00 PM	10	FALSE	FALSE	-	-	-	-
2019-11-09 07:00 PM	40	TRUE	FALSE	6	37	5	7.40
2019-11-09 08:00 PM	19	FALSE	FALSE	-	-	-	-
2019-11-09 09:00 PM	15	FALSE	FALSE	-	-	-	-
2019-11-09 10:00 PM	10	FALSE	FALSE	-	-	-	-
2019-11-09 11:00 PM	5	FALSE	FALSE	-	-	-	-
2019-11-10 12:00 AM	3	FALSE	TRUE	-	-	-	-
2019-11-10 01:00 AM	7	TRUE	FALSE	4	5	3	1.67
2019-11-10 02:00 AM	6	FALSE	FALSE	-	-	-	-
2019-11-10 03:00 AM	4	FALSE	FALSE	-	-	-	-
2019-11-10 04:00 AM	2	FALSE	TRUE	-	-	-	-
2019-11-10 05:00 AM	3	FALSE	FALSE	-	-	-	-
2019-11-10 06:00 AM	4	FALSE	FALSE	-	-	-	-

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

 

[Legacy 452786]

Like this? Tried to post a bigger example but seems it was moderated away:

Date-time	value	Is highest in cycle	Is lowest in cycle	steps highest-to-lowest	Highest-to-lowest difference	Highest-to-lowest hours	Change rate per hour
2019-11-09 07:00 AM	7	TRUE	FALSE	4	5	3	1.67
2019-11-09 08:00 AM	6	FALSE	FALSE	-	-	-	-
2019-11-09 09:00 AM	4	FALSE	FALSE	-	-	-	-
2019-11-09 10:00 AM	2	FALSE	TRUE	-	-	-	-
2019-11-09 11:00 AM	3	FALSE	FALSE	-	-	-	-
2019-11-09 12:00 PM	5	TRUE	FALSE	2	3	1	3.00
2019-11-09 01:00 PM	2	FALSE	TRUE	-	-	-	-

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

 

[Daniel_CW]

time	value
4am	2
5am	3
6am	6
7am	7
8am	8
9am	7
10am	6
11am	5
12pm	3
1pm	2
2pm	4
3pm	5
4pm	6
5pm	9
6pm	7
7pm	5
8pm	4
9pm	3
10pm	2
11pm	4
12am	5
1am	6
2am	8
3am	7
4am	5

<tbody>
</tbody>

4am - 2
8am - 8

(8-2)/(8am-4am)
6/4hrs
=1.5
then

1pm - 2
5pm - 9

(9-2)/(5pm-1pm)
7/4hrs
=1.75

Welcome to the MrExcel board!

What about you finish the one-day sample and also provide the expected result(s) for that sample with some explanation of how you got the result manually?

 

[Peter_SSs]

Thanks for the additional sample.
See if these would suffice. You could hide column C once populated with its formulas if you want.

00 HTML Conversions.xlsm
	A	B	C	D
1	time	value
2	4:00 AM	2	1
3	5:00 AM	3
4	6:00 AM	6
5	7:00 AM	7
6	8:00 AM	8	x	1.5
7	9:00 AM	7
8	10:00 AM	6
9	11:00 AM	5
10	12:00 PM	3
11	1:00 PM	2	1
12	2:00 PM	4
13	3:00 PM	5
14	4:00 PM	6
15	5:00 PM	9	x	1.75
16	6:00 PM	7
17	7:00 PM	5
18	8:00 PM	4
19	9:00 PM	3
20	10:00 PM	2	1
21	11:00 PM	4
22	12:00 AM	5
23	1:00 AM	6
24	2:00 AM	8	x	-0.3
25	3:00 AM	7
26	4:00 AM	5
Min Max Sequence

Cell Formulas
Range	Formula
C2:C26	C2	=IF(AND(B2>B3,B2>B1),"x",IF(AND(B2<B3,B2<B1),1,""))
D2:D26	D2	=IF(C2="x",(B2-LOOKUP(2,C$1:C1,B$1:B1))/((A2-LOOKUP(2,C$1:C1,A$1:A1))*24),"")