Question: How do I divide values equally into groups (3 lists or less)?

split-values-equally-into-groups-using-excel-array-formula1

Answer:

split-values-equally-into-groups-using-excel-array-formula

Formula in c4:C25:

=IF(D1>0, INDEX($A$2:$A$21, (ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-1)), "") + CTRL + SHIFT + ENTER

Formula in D4:D25:

=IF(D1>1, INDEX($A$2:$A$21, (ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-2)), "") + CTRL + SHIFT + ENTER

Formula in D4:D25:

=IF(D1>2, INDEX($A$2:$A$21, (ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-3)), "") + CTRL + SHIFT + ENTER

Try changing value (1, 2 or 3) in D1 and see how List1, List2 and List3 changes.

Download example workbook

Download excel sample file for this tutorial.
split-values-into-groups-using-excel-formula.xls
(Excel 97-2003 Workbook *.xls)


Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference)
Returns the rownumber of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text