Min between two values without a defined nth range

[voltrader]

Hey All,

Been working on this for a while and couldn't figure it out, tried offset, and Match/INDEX. I still cant get around the issue of the range not being a consistent nth row.[TABLE="width: 500"]
<tbody>[TR]
[TD]10:00[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]10:01[/TD]
[TD]95%[/TD]
[/TR]
[TR]
[TD]10:02[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]10:03[/TD]
[TD]95%[/TD]
[/TR]
[TR]
[TD]10:04[/TD]
[TD]92%[/TD]
[/TR]
</tbody>[/TABLE]

As the above table illustrates the range does not hold a pattern of every nth row.I need to create a moving "anchor" to say "from each 100% find min until the next 100%.

Thanks!

 

[Johnny Thunder]

What??? I have no idea what you are asking for...

Hey All,

Been working on this for a while and couldn't figure it out, tried offset, and Match/INDEX. I still cant get around the issue of the range not being a consistent nth row.[TABLE="width: 500"]
<tbody>[TR]
[TD]10:00[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]10:01[/TD]
[TD]95%[/TD]
[/TR]
[TR]
[TD]10:02[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]10:03[/TD]
[TD]95%[/TD]
[/TR]
[TR]
[TD]10:04[/TD]
[TD]92%[/TD]
[/TR]
</tbody>[/TABLE]

As the above table illustrates the range does not hold a pattern of every nth row.I need to create a moving "anchor" to say "from each 100% find min until the next 100%.

Thanks!

 

[sheetspread]

More efficient than the single-formula approach:

Excel 2010
	A	B	C
1	10:00	100%	1
2	10:01	95%	1
3	10:02	100%	2
4	10:03	95%	2
5	10:04	92%	2
6	10:05	98%	2
7	10:06	94%	2
8	10:07	100%	3
9	10:08	93%	3
10	10:09	93%	3
11	10:10	90%	3
Sheet1

Cell Formulas
Range	Formula
C1	=COUNTIF($B$1:B1,1)

then you can run a pivot table or subtotaler and have the min at each group change.

 

[voltrader]

What??? I have no idea what you are asking for...

I need to find the MIN value for each cell between 100%. I am not looking for the amount of MIN values.[TABLE="width: 500"]
<tbody>[TR]
[TD]10:01[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:02[/TD]
[TD]95%[/TD]
[TD]92%[/TD]
[/TR]
[TR]
[TD]10:03[/TD]
[TD]92%[/TD]
[TD]92%[/TD]
[/TR]
[TR]
[TD]10:04[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:05[/TD]
[TD]91%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]10:06[/TD]
[TD]80%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]10:07[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

If you want to repeat the min values next to each percent that isn't 100:

Excel 2010
	A	B	C	D
1	time	percent	group	min
2	10:00	100%	1
3	10:01	95%	1	0.95
4	10:02	100%	2
5	10:03	95%	2	0.92
6	10:04	92%	2	0.92
7	10:05	98%	2	0.92
8	10:06	94%	2	0.92
9	10:07	100%	3
10	10:08	93%	3	0.9
11	10:09	93%	3	0.9
12	10:10	90%	3	0.9
Sheet1

Cell Formulas
Range	Formula
C2	=COUNTIF($B$2:B2,1)
D2	{=IF(B2=1,"",MIN(IF($C$2:$C$12=C2,$B$2:$B$12)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[voltrader]

If you want to repeat the min values next to each percent that isn't 100:

Excel 2010

	A	B	C	D
time	percent	group	min

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10:01[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10:02[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10:03[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.92[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]10:04[/TD]
[TD="align: right"]92%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.92[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]10:05[/TD]
[TD="align: right"]98%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.92[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]10:06[/TD]
[TD="align: right"]94%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.92[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10:07[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10:08[/TD]
[TD="align: right"]93%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.9[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10:09[/TD]
[TD="align: right"]93%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.9[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10:10[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.9[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=COUNTIF($B$2:B2,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IF(B2=1,"",MIN(IF($C$2:$C$12=C2,$B$2:$B$12)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

THAT WORKS! thanks so much!