# Min and Max in sequence

#### Daniel_CW

##### New Member
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

##### MrExcel MVP, Moderator
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?

Last edited:

#### Regex

##### New Member
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>

#### Regex

##### New Member
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

##### New Member
 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

##### MrExcel MVP, Moderator
See if these, copied down would suffice. You could hide column C once populated with its formulas if you want.

<b>Min Max Sequence</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:52px;" /><col style="width:35px;" /><col style="width:47px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">time</td><td style="font-size:10pt; text-align:right; ">value</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">4:00 AM</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">5:00 AM</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">6:00 AM</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">7:00 AM</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">8:00 AM</td><td style="font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; text-align:center; ">x</td><td style="font-size:10pt; text-align:right; ">1.5</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">9:00 AM</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">10:00 AM</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">11:00 AM</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">12:00 PM</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">1:00 PM</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">2:00 PM</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">3:00 PM</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">4:00 PM</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">5:00 PM</td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; text-align:center; ">x</td><td style="font-size:10pt; text-align:right; ">1.75</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">6:00 PM</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">7:00 PM</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">8:00 PM</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">9:00 PM</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">10:00 PM</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:center; ">1</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; text-align:right; ">11:00 PM</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; text-align:right; ">12:00 AM</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; text-align:right; ">1:00 AM</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; text-align:right; ">2:00 AM</td><td style="font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; text-align:center; ">x</td><td style="font-size:10pt; text-align:right; ">1.5</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:10pt; text-align:right; ">3:00 AM</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-size:10pt; text-align:right; ">4:00 AM</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(AND<span style=' color:008000; '>(B2>B3,B2>B1)</span>,"x",IF<span style=' color:008000; '>(AND<span style=' color:#0000ff; '>(B2<B3,B2<B1)</span>,1,"")</span>)</td></tr><tr><td >D2</td><td >=IF(C2="x",<span style=' color:008000; '>(B2-LOOKUP<span style=' color:#0000ff; '>(2,C\$1:C1,B\$1:B1)</span>)</span>/<span style=' color:008000; '>(<span style=' color:#0000ff; '>(A2-LOOKUP<span style=' color:#ff0000; '>(2,C\$1:C1,A\$1:A1)</span>)</span>*24)</span>,"")</td></tr></table></td></tr></table>

Last edited:

1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...