Find MIN within a range & count how many consecutive negative numbers until the MIN

[kylc3]

Let's say I have a column with the following values:

...
-0.26%
-0.09%
0.00%
0.00%
-0.25%
-0.82%
-1.08%
-1.09%
-0.98%
-1.27%
-1.04%
-1.78%
-3.46%
-2.80%
0.00%
-1.39%
...

Is there a formula that can:

Identify the MIN (-3.46%), then count the # of consecutive negative #s from above the MIN until it reaches it (including the MIN). In this example, I'd like the result of the formula to be 9.

Hopefully this request makes sense. Thanks in advance!

 

[Eric W]

Welcome to the forum.

Try:

	A	B	C
1	Values
2	-0.26%
3	-0.09%		9
4	0.00%
5	0.00%
6	-0.25%
7	-0.82%
8	-1.08%
9	-1.09%
10	-0.98%
11	-1.27%
12	-1.04%
13	-1.78%
14	-3.46%
15	-2.80%
16	0.00%
17	-1.39%

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C3 {=MATCH(MIN(A2:A17),A2:A17,0)-LARGE(IF((A2:A17>=0)*(ROW(A2:A17)-ROW(A2)+1< MATCH(MIN(A2:A17),A2:A17,0)),ROW(A2:A17)-ROW(A2)+1),1)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[DRSteele]

Welcome to the forum. I think I concocted something that works. Note that in cases where the minimum value exists more than once in the DATA, the formula will act on only the first occurrence.

	A	B
1	DATA
2	-0.26%
3	-0.09%
4	0.00%
5	0.00%
6	-0.25%
7	-0.82%
8	-1.08%
9	-1.09%
10	-0.98%
11	-1.27%
12	-1.04%
13	-1.78%
14	-3.46%
15	-2.80%
16	0.00%
17	-1.39%
18		Streak of negatives before Minimum
19		9

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula B19 {=IF(INDEX(A2:A17,MATCH(MIN(A2:A17),A2:A17,0)-1)>=0,0,MAX(FREQUENCY(IF(OFFSET(A2,,,MATCH(MIN(A2:A17),A2:A17,0))<0,ROW(OFFSET(A2,,,MATCH(MIN(A2:A17),A2:A17,0)))),IF(OFFSET(A2,,,MATCH(MIN(A2:A17),A2:A17,0))>=0,ROW(OFFSET(A2,,,MATCH(MIN(A2:A17),A2:A17,0)))))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[DRSteele]

Eric, I think this formula reports an incorrect result when the number prior to the MIN value is not a negative. For example, change A14 to 0. The MIN value would then be -2.80% and the value above it would be zero, so the formula should report 0 and not 1 as it does.

 

[Marcelo Branco]

Eric, I think this formula reports an incorrect result when the number prior to the MIN value is not a negative. For example, change A14 to 0. The MIN value would then be -2.80% and the value above it would be zero, so the formula should report 0 and not 1 as it does.

I think it should return 1 - see below in bold

Identify the MIN (-3.46%), then count the # of consecutive negative #s from above the MIN until it reaches it (including the MIN). In this example, I'd like the result of the formula to be 9.

Maybe this regular formula also works.
=MATCH(MIN(A2:A17),A2:A17,0)-LOOKUP(2,1/(A2:INDEX(A2:A17,MATCH(MIN(A2:A17),A2:A17,0))>=0),ROW(A2:A17)-ROW(A2)+1)

M.

 

[jmcdonald22]

Another way (easier for people like me) would be to type this formula in B1 (assuming your data starts in A1). This will require you to create a new column...FYI

=IF(A1<0,1,0)

Then, in B2 (under the above formula) type this formula and copy down:

=IF(A2<0,B1+1,0)

After this step, put this function where you would like the output to be:

=MAX(A:A)


To find the minimum, you can simply use the MIN function:

=MIN(A:A)

Hope this helps.

 

[Eric W]

@DRSteele:

You may be right. However, the example from the OP gave a result of 9. This means that the minimum value was included in the count, otherwise the expected result would have been 8. So I designed the formula to give a minimum value of 1 (or #NUM if there are no negative values). We may need clarification from the OP.

@Marcelo: Clever formula!

 

[kylc3]

Wow, that was fast! Thank you all for the feedback! I applied DRSteele's formula and it worked great! I need to build out some more functionality in the file, but first I'll have to figure out what the heck I need it to output. It's awesome to know I can reach out to this great forum when I inevitably get stuck. Thx again!

 

[Marcelo Branco]

@DRSteele:

You may be right. However, the example from the OP gave a result of 9. This means that the minimum value was included in the count, otherwise the expected result would have been 8. So I designed the formula to give a minimum value of 1 (or #NUM if there are no negative values). We may need clarification from the OP.

@Marcelo: Clever formula!

@Eric
Thank you

If there are no negative values, my formula returns 0 - it seems Ok for me, unless OP thinks differently

M.

 

[Marcelo Branco]

oops...it seems that no suggested formula works if the min is first value (A2 in this case)

So i've adjusted my formula (post #5) to
=MATCH(MIN(A2:A17),A2:A17,0)-IFERROR(LOOKUP(2,1/(A2:INDEX(A2:A17,MATCH(MIN(A2:A17),A2:A17,0))>=0),ROW(A2:A17)-ROW(A2)+1),0)

M.