sort variable length column for min value--number of rows is variable

**dloe** · 04-13-2012, 03:10 PM

I need to find the mininum number from a column. My range needs to start with cell b2 and stop with first cell it encounters that has text. "TOT"

**Marcol** · 04-13-2012, 03:16 PM

Try this

=MIN(B2:INDEX(B:B,MATCH("TOT",B:B,0)))

**protonLeah** · 04-13-2012, 03:18 PM

We need a better description of your problem/worksheet. Will there be more numbers below the cell with "tot"?

**dloe** · 04-13-2012, 04:22 PM

one column only-- has TOT at top and TOT at bottom with numbers in between, then it repeats with a different set of numbers and TOT at bottom again.....I need smallest number from each group of numbers between the TOT

**Marcol** · 04-13-2012, 08:25 PM

Post a typical workbook.

My range needs to start with cell b2 and stop with first cell it encounters that has text. "TOT"

My first response should have solved your initial query, but now you have to handle groups of data, that's an entirely different problem.

**dloe** · 04-13-2012, 09:37 PM

I need a formula that will find the smallest number in each group between TOT. That will stay the same, if I delete a row, or add a row, or sort numbers,
answer for each group will be in cell beside the top TOT of that group.

TOT
56
54
48
62
63
TOT
45
64
66
69
55
48
TOT
63
44
55
TOT

**Marcol** · 04-14-2012, 04:07 AM

Why not post a sample of your workbook as an attachment? It makes life easier all round.

Try this example workbook.

With your data in Column B as shown.
In A1

=IF(ROW()=1,1,IF(B1="TOT",ROW(),""))

Drag/Fill Down as required

In C2

=IF(B2="TOT",MIN(INDEX(B:B,MAX($A$1:$A1)):INDEX(B:B,ROW())),"")

Drag/Fill Down as required.

The helper, Column A, can be hidden, or use a grouping button as in this example.

Note the conditional formatting in Columns B & C.