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

Answer:

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

Related posts:

1. Extract unique values from a range using array formula in excel
2. Extract largest values from two columns using array formula in excel
3. Sort text values by length using array formula in excel
4. Filter text values existing in range 1 but not in range 2 using array formula in excel
5. Sort values in parallel (array formula)
6. Filter common values between two ranges using array formula in excel
7. Min and max unique and duplicate values using array formula in excel
8. Filter values existing in range 1 but not in range 2 using array formula in excel
9. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
10. Split first and last names in excel and then sort them alphabetically