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":


[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Count[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]




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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

U​

[/td][td]

V​

[/td][td]

W​

[/td][/tr]
[tr][td]

1​

[/td][td] Count[/td][td][/td][td][/td][/tr]


[tr][td]

2​

[/td][td] 1[/td][td][/td][td] 2[/td][/tr]


[tr][td]

3​

[/td][td] 3[/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td] 1[/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td] 2[/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td] 3[/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td] 4[/td][td][/td][td][/td][/tr]


[tr][td]

8​

[/td][td] 1[/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td] 2[/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td] 3[/td][td][/td][td][/td][/tr]


[tr][td]

11​

[/td][td] 1[/td][td][/td][td][/td][/tr]


[tr][td]

12​

[/td][td] 2[/td][td][/td][td][/td][/tr]


[tr][td]

13​

[/td][td] 1[/td][td][/td][td][/td][/tr]


[tr][td]

14​

[/td][td] 2[/td][td][/td][td][/td][/tr]


[tr][td]

15​

[/td][td] 3[/td][td][/td][td][/td][/tr]


[tr][td]

16​

[/td][td] 4[/td][td][/td][td][/td][/tr]
[/table]

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.