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:
Formula in D4:D25:
Formula in D4:D25:
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:
Text to columns: Split words in a cell (excel array formula)
Extract largest values from two columns using array formula in excel
Extract unique values from a range using array formula in excel



















i tried to add a list number 4 with this formula =IF(D1>3,INDEX($A$2:$A$21,(ROW($A$2:$A$21)-MIN(ROW($A$2:$A$21))+1)*D1-(D1-4)),"")
the value at f4 is DD but the f5, f6,etc....arejust repeating the result
Fahmy,
split-values-into-groups-using-excel-formula.xlsx
Hi Oscar, thanks for the great tutorial.
I was hoping to use what you've described above but for data that stretches across a single row.
For example, if I have data that stretches from A1:J1, I would like to split it up into 5 rows so that I would have the values appear in A2:B2, A3:B3, A4:B4,A5:B5, A6:B6.
I have taken a stab at manipulating the formulas you've provided but with no luck. Was hoping you could help out!
Thanks!
Hasan,
Array formula in cell range: A2:E3:
How to create array formula
1. Select cell range A2:E3
2. Paste formula in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter
Adjust bolded cell ranges if you enter the array formula in a different cell range.
Hasan1.xlsx