Determining minimum of ranges separated by blanks

[waybrendan]

Hello,

I am trying to determine the minimum price in several groups separated by blanks with varying quantities of cells. For example:.

Column D

1.226
1.666
1.233

1.846
1.899

1.555
1.963
1.877
1.856

There are no common names in other columns that link the groups together.. otherwise I think I could use a pivot. Can anybody help me out? either with a regular function or a macro? I would like to be able to return a value in each cell of Column E.

Thank you very much!

Brendan

[XOR LX]

Hi.

So can you add your expected results to that table?

Regards

[waybrendan]

Yes, I would likem them to appear in the next column over.

Thank you

[XOR LX]

No! I meant can you add some manually-calculated results to your post so that we know what results we're aiming for.

Regards

[oeldere]

With 2 helpcolumns (just column B will do, with the formula below).

After that an pivot table.

See the attached file.

B4=if(A4="","","Group "&countif($A$4:$A4,"")+1) and drag down.

[waybrendan]

Sure, sorry about that. Here is what i'm looking for:

Column D Column E

1.226 1.226
1.666 1.226
1.233 1.226


1.899 1.846
1.846 1.846

1.555 1.554
1.963 1.554
1.554 1.554
1.856 1.554



Thank you

[waybrendan]

Im' sorry oeldere, but I don't have common group names to create a pivot with! assigning common names would take as long as manual analysis to determine the minimums

[waybrendan]

Thanks oeldere, it works great!

[oeldere]

I understand the question as follow:

1) determine which data belongs in the same group.

2) after that I (you) be able to get the minimum value (e.g.) with an pivot table.

The formulat I gave you will solve 1).

[waybrendan]

excellent, how can i close the thread?

[oeldere]

Since you are new on the forum, you can add rep(utationpoints) to the one's who helped you by clicking on the star on the left side.

You can close the question above the your first question (#1)

[newdoverman]

Enter this formula in A2 and fill down. Data is in B2:B12

Please Login or Register  to view this content.

Result:

	A	B
2	1	1.226
3	1	1.666
4	1	1.233
5
6	2	1.846
7	2	1.899
8
9	3	1.555
10	3	1.963
11	3	1.877
12	3	1.856

Enter this ARRAY formula in C2 and fill down: (Enter with Ctrl + Shift + Enter)

Please Login or Register  to view this content.

Result

	A	B	C
2	1	1.226	1.226
3	1	1.666	1.226
4	1	1.233	1.226
5
6	2	1.846	1.846
7	2	1.899	1.846
8
9	3	1.555	1.555
10	3	1.963	1.555
11	3	1.877	1.555
12	3	1.856	1.555