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

0 votes

enter image description here

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 by Kithuzzz
• 28,900 points
245 views

1 answer to this question.

0 votes

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"),"")
answered Oct 17, 2022 by narikkadan
• 53,520 points

Related Questions In Others

+1 vote
1 answer

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

answered Dec 24, 2022 in Others by narikkadan
• 53,520 points
71 views
0 votes
1 answer
0 votes
1 answer

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

answered Jan 21 in Others by narikkadan
• 53,520 points
49 views
0 votes
0 answers

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

I have this set of numbers. 74, 15, ...READ MORE

1 day ago in Others by Kithuzzz
• 28,900 points
24 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
• 13,630 points
106 views
0 votes
1 answer

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

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

answered Oct 15, 2022 in Others by narikkadan
• 53,520 points
214 views
0 votes
0 answers

How to create DropDown which have dynamic Validation List

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

Oct 31, 2022 in Others by Kithuzzz
• 28,900 points
143 views
0 votes
0 answers

Excel formula converting tan to angle

I have an excel spreadsheet that computes ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 28,900 points
54 views
0 votes
1 answer

MAX function in Excel: is it possible to provide the range by means of variables?

Try this: =MAX(INDEX(A:A,B2):INDEX(A:A,B3)) READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 53,520 points
63 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP