Split data with a equal total (approximately)

Hi friends,

I have some data with different numbers. I want to split that data in to 5 equal columns(approx.). Every time I have to do this manually, and its taking too much time.

Is there any possibility with the excel formulas to get result.

Please find attachment for better understanding.

Note : Values no need to come in same order which was in worksheet.

Hi maras_mak,

With the above formula values coming in order from top to bottom, is there any way to start with min value to max value and total should be close like that.

Thank you for your help.

I think your question is unclear and you wish to go left to right on row 1, then right to left on row2 and keep snaking like this till you get to the end of your data, and as the data is sorted this results in roughly equal groups
=INDEX($A$2:$A$31,IF(ISEVEN( ROW(1:1)),ROW(A1)*5-COLUMN(A1)+1,5*ROW(1:1)-4+MOD(COLUMN(A$1)-1,5)))

this mimics your example data

Thank you davsth,

Actually numbers will always change, I want to share into 5 parts with same total (max. possibility). Some times numbers will be like below.

21.540
15.430
24.550
25.400
21.260
25.780
25.410
25.870
15.270
23.900
20.100
12.440
17.500
25.890
15.100
26.700
21.280
14.890
23.670
19.230
18.710
13.590

I want to split / share above numbers in 5 groups, all groups total will be same (maximum possibility)

Thank you very much to giving me your valuable time.

really you need to solve as an optimization problem t get the best answer. however your method if the data isn't sorted

=SMALL($A$2:$A$31,IF(ISEVEN( ROW(1:1)),ROW(A1)*5-COLUMN(A1)+1,5*ROW(1:1)-4+MOD(COLUMN(A$1)-1,5)))