Excel function for divide or split number to maximum possible equal parts

Question

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

Answer 1

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"),"")