Help me find the time when a value starts to go above the set level

[zacuk]

Hi,

In a process, a value first decreases and then increases back up again, over time. Would be great if someone can help me calculate the time when the value is increasing and has gone past a certain level.

So for example, in the following example, I would like Excel to return "7 hour" (because this is when the 'Value' climbs back up to greater than my set threshold, 5). Please note that I would like the formula/macro to ignore the initial hours when the 'value' was greater than 5 BUT it was not increasing (i.e., it was initially decreasing):

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Time (hour)[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5.5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

















A formula would be great, otherwise a macro will also do. Thanks

 

[NARAYANK991]

Hi ,

See if this works :

=INDEX($A$3:$A$10, MATCH(1, ($B$2:$B$9 <= 5 ) * ($B$3:$B$10 >= 5), 0))

This is an array formula , to be entered using CTRL SHIFT ENTER.

 

[zacuk]

Thanks Narayank. Great help. It worked perfectly fine in the initial test. I need to use if over multiple batches to see if it works every time or not.

Just out of curiosity, could you tell a little bit about what logic is used in your formula, please? Thanks.

 

[NARAYANK991]

Hi ,

The logic is that we want to detect a transition point ; we are looking for a pair of cells , where the first cell will have a value lower than the threshold , and the second cell will have a value higher than the threshold.

But when we have two results like this , they will be two elements of an array of results ; if we can ensure that there is only one result which is the AND of both the above checks , we can be sure we have our transition point.

This is the reason the first array uses the elements in rows 2 through 9 i.e. the first 8 elements of the range , and the second array uses the elements in rows 3 through 10 i.e. the next 8 elements of the range. Since each array still has the same number of elements , we can multiply the two of them together , which is equivalent to ANDing the two.

Where ever the two conditions were not satisfied , either because the values kept on increasing or because the values kept on decreasing , the resulting array will have 0 ; only when we reach the transition point , both conditions will be satisfied , and the result will be a 1 in the array.

The only thing to remember is that the INDEX parameter should be the range consisting of rows 3 through 10 rather than rows 2 through 9 , since the first range is for seeing which element is less than the threshold ; the second range is for seeing which element is greater than the threshold.

I don't think I have been able to explain thoroughly , but I think you might have understood.

 

[zacuk]

Thanks a lot for the explanation, most of which does make a sense to me. The only thing I can't get my head around is how the outcome of ($B$2:$B$9 <= 5 ) * ($B$3:$B$10 >= 5) is ONE!

Obviously, it must be true because it works! But, I just want to know what ($B$2:$B$9 <= 5 ) and ($B$3:$B$10 >= 5 actually find... do they return Row Number or the actual values? Multiplying either of these does not give 1..!

 

[NARAYANK991]

Hi ,

If we consider the data you have posted , then the range B2 through B10 will have the following values :

8 , 7 , 6 , 1 , 0.5 , 2 , 3 , 5.5 , 7

We now split this into two ranges :

8 , 7 , 6 , 1 , 0.5 , 2 , 3 , 5.5

and

7 , 6 , 1 , 0.5 , 2 , 3 , 5.5 , 7

Now we apply the checks , as to which elements in the first range are <= 5 , and we will get an array of results , as follows :

FALSE , FALSE , FALSE , TRUE , TRUE , TRUE , TRUE , FALSE (8 , 7 , 6 , 1 , 0.5 , 2 , 3 , 5.5)

Next we apply the check to the second range , as to which elements are >= 5 , and we will get an array of results , as follows :

TRUE , TRUE , FALSE , FALSE , FALSE , FALSE , TRUE , TRUE (7 , 6 , 1 , 0.5 , 2 , 3 , 5.5 , 7)

When we multiply these two arrays , all elements except the 7th element will be 0. Only the 7th elements in both arrays are TRUE , and so the result will be 1.

This is identified using the MATCH function.

 

[zacuk]

Great stuff. You are Mr. Excel

Actually, I just learnt something new that false/true can be multiplied in Excel. Just checked, only true*true = 1; false*false is still 0

Amazing.


PS: likewise, true,false can be divided too, but false/false gives error.. don't know why..

 

[NARAYANK991]

Hi ,

False is the equivalent of 0 , and hence False/False will generate a #DIV/0! error.