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.

10:00	100%
10:01	95%
10:02	100%
10:03	95%
10:04	92%

<tbody>
</tbody>

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.

10:00	100%
10:01	95%
10:02	100%
10:03	95%
10:04	92%

<tbody>
</tbody>

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.

10:01	100%
10:02	95%	92%
10:03	92%	92%
10:04	100%
10:05	91%	80%
10:06	80%	80%
10:07	100%

<tbody>
</tbody>

 

[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
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

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

Sheet1​

Worksheet Formulas Cell Formula C2 =COUNTIF($B$2:B2,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=IF(B2=1,"",MIN(IF($C$2:$C$12=C2,$B$2:$B$12)))} <thead> </thead><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>

THAT WORKS! thanks so much!