 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 1,504 views

## 1 answer to this question.

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,180 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