Split values equally into groups using excel array formula
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:
- Extract unique values from a range using array formula in excel
- Extract largest values from two columns using array formula in excel
- Sort text values by length using array formula in excel
- Filter text values existing in range 1 but not in range 2 using array formula in excel
- Sort values in parallel (array formula)
- Filter common values between two ranges using array formula in excel
- Min and max unique and duplicate values using array formula in excel
- Filter values existing in range 1 but not in range 2 using array formula in excel
- Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
- Split first and last names in excel and then sort them alphabetically




Leave a Reply