I need to split(like divide) by 5, however, each value should balance maximum possible way to each part

Example

```6= 3,3 is ok.  but 6= 5,1 is wrong
```

```18= 5,5,4,4 is ok. but 18= 5,5,5,3 is wrong
```

`21= 5,4,4,4,4 is ok. but 21= 5,5,5,5,1 is wrong`
Oct 16, 2022 in Others 2,102 views

The underlying math for this is as follows. A number have to split will be split into repeats of two numbers. The smaller number is given by:

```=QUOTIENT(NUMBER_HAVE_TO_SPLIT,SPLIT_BY_PARTS)
```

And the larger number is given by:

```=QUOTIENT(NUMBER_HAVE_TO_SPLIT,SPLIT_BY_PARTS)+1
```

FYI the QUOTIENT worksheet function does integer division, e.g, QUOTIENT(13,4)=3.

The number of times the larger number is repeated is given by:

```=MOD(NUMBER_HAVE_TO_SPLIT,SPLIT_BY_PARTS)
```

And the number of times the smaller number is repeated is given by:

```=SPLIT_BY_PARTS - MOD(NUMBER_HAVE_TO_SPLIT,SPLIT_BY_PARTS)
```

The remaining task is to return the results in the formats you suggest. To get the comma-delimited format 3,4,4:

1. Convert both smaller numbers and larger numbers to text using TEXT(number,0)
2. Prepend each with a comma to give the strings,3 and,4
3. Use the REPT function to repeat each the appropriate number of times to give,3 and,4,4
4. Concatenate these two strings and use SUBSTITUTE to remove the first comma

A somewhat messy formula to accomplish the above is:

```=SUBSTITUTE(REPT(","&TEXT(QUOTIENT(A2,B2),"0"),B2-MOD(A2,B2))&REPT(","&TEXT(QUOTIENT(A2,B2)+1,"0"),MOD(A2,B2)),",","",1)
```

where NUMBER_HAVE_TO_SPLIT and SPLIT_BY_PARTS are in A2 and B2, respectively.

A formula to generate the 3=1 and 4=2 format is

```=TEXT(QUOTIENT(A2,B2),0)&"="&TEXT(B2-MOD(A2,B2),"0")&IF(MOD(A2,B2)>0," and "&TEXT(QUOTIENT(A2,B2)+1,0)&"="&TEXT(MOD(A2,B2),"0"),"")
```
• 63,700 points

+1 vote

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

Excel 365 Formulas - How to save LAMBDA function permanently (for all existing and new files)?

Try creating a template for excel to ...READ MORE

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

In excel how do I reference the current row but a specific column?

Put a \$ symbol in front of ...READ MORE

How to create DropDown which have dynamic Validation List

In my data table, the columns "Category" ...READ MORE