Minimum of values from index of array values wanted [CSE]

[cruisen]

Hope I present this problem in an understandable way...



1.) DATA

I have an "up counting", table like this, which restarts the count from 1 "randomly":

Count
2
3
1
2
3
4
1
2
3
1
2
1
2
3
4

<tbody>
</tbody>

2.) GOAL

• It is easy to find the maximum: 4
• and I do not need the minimum value of the table [=1]
• but I need the minimum value of the count, just before it starts again with 1, which in this case is 2.

3.) APPROACH (working part)

If the column is "U", then I get an array with the row numbers of the values before the next "1" with:

={IF($U:$U=1,ROW($U:$U)-1,FALSE)}


4.) TEST

I tested it and it works, since i can access individual values with the small function.

={INDEX($U:$U,SMALL(IF($U:$U=1,ROW($U:$U)-1,FALSE),2))}


5.) APPROACH (not working part)

However, in order to get the minimum of the "all" values, i tried:

={MIN(INDEX($U:$U,IF($U:$U=1,ROW($U:$U)-1,FALSE)))}

which does not work. It returns "1".


6.) GUESS

I guess it is wrong since INDEX does not return an array, but a single value.



7.) QUESTION

Maybe I am on a wrong track, or I am missing something, but I can not solve this one.

Any help / hints / ideas welcome.

Thank you!

 

[Aladin Akyurek]

Does this help?

Control+shift+enter, not just enter:

=MIN(IF($U$3:$U$16=1,$U$2:$U$15))

 

[cruisen]

Thank you, but i used CSE (Control+shift+enter).

 

[Aladin Akyurek]

Thank you, but i used CSE (Control+shift+enter).

I don't understand what you mean. The formula I am suggesting requires control+shift+enter...

 

[cruisen]

Sorry to be unclear, i used CTRL+SHIFT+ENTER to enter my original formulas.

And CSE is an abbreviation for formulas entered with CTRL+SHIFT+ENTER.

So this is not the problem.

---

and BTW, your formula would compute the minimum of all "1" values ($U$3:$U$16=1), which is 1.

---

Thank you for your time you spend trying to help.

 

[cruisen]

I hope my original post #1 is not to complicated for this forum

Please let me know if you have questions regarding the still unsolved problem.

 

[Aladin Akyurek]

Sorry to be unclear, i used CTRL+SHIFT+ENTER to enter my original formulas.

And CSE is an abbreviation for formulas entered with CTRL+SHIFT+ENTER.

So this is not the problem.

---

and BTW, your formula would compute the minimum of all "1" values ($U$3:$U$16=1), which is 1.

---

Thank you for your time you spend trying to help.

The formula computes 2 as result, not 1.

Row\Col	U​	V​	W​
1​	Count
2​	1		2
3​	3
4​	1
5​	2
6​	3
7​	4
8​	1
9​	2
10​	3
11​	1
12​	2
13​	1
14​	2
15​	3
16​	4

In W2 control+shift+enter, not just enter:

=MIN(IF($U$3:$U$16=1,$U$2:$U$15))

Note the ranges specifications: U3:U16 vs U2:U15. The minimum is computed from the latter.

Just to make sure, control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel itself puts a pair of { and } around the formula.

 

[cruisen]

Dear Aladin,

this is a very smart Solution!

In W2 control+shift+enter, not just enter:

=MIN(IF($U$3:$U$16=1,$U$2:$U$15))

Note the ranges specifications: U3:U16 vs U2:U15. The minimum is computed from the latter.

( and sorry, when looking at your answer, I Missed the shift of rows by one between the two given ranges.)

Thank you.

cruisen

 

[Aladin Akyurek]

Dear Aladin,

this is a very smart Solution!



( and sorry, when looking at your answer, I Missed the shift of rows by one between the two given ranges.)

Thank you.

cruisen

You are welcome.