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
• 38,010 points
2,102 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
• 63,700 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
• 63,700 points
678 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, 2023 in Others by narikkadan
• 63,700 points
321 views
0 votes
1 answer

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

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
457 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,670 points
282 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
• 63,700 points
1,448 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
• 38,010 points
462 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
• 38,010 points
256 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
• 63,700 points
377 views
0 votes
1 answer
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