+ Reply to Thread
Results 1 to 4 of 4

number of equal parts in "n" number of groups in excel

  1. #1
    Registered User
    Join Date
    03-01-2020
    Location
    ksu
    MS-Off Ver
    360
    Posts
    2

    Red face number of equal parts in "n" number of groups in excel

    Hello

    I am a novice at using excel, although I am learning and am proud of what I have learned so far.
    I am sure that others have asked the same question I am asking now, however I did not come across any solution that fits my needs.

    I am aware that to some of you my question may seems silly and obvious, so I apologies in advance, but to me its like trying to find a needle in a haystack. so here goes nothing:

    I have a problem with:" splitting a value into multiple equal (as much as possible) parts based on specific number.

    example:
    column A (Levels) Column B (number of employ) Column C (number of groups)
    level 3 35 3 groups
    level 4 8 1 groups
    level 5 22 2 groups
    level 6 10 6 groups
    level 7 1 1 groups
    level 8 0 0 groups

    Note: in Column C
    number of groups ( maximum number of groups possible at any given time is 8 groups)



    so in new columns (F:M) I want to split (the total number of employ)based on (the number of groups) for each level and show that across different columns (F:M):

    column E (LEVEL) column F (1 G) Column G (2 G) Column H (3 G) Column I (4 G) Column J (5 G) Column K (6 G) Column L (7 G) Column M (8 G)
    level 3,totola 35,split to 3 groups 12 12 11 0 0 0 0 0
    level 4,totola 38,split to 1 groups 8 0 0 0 0 0 0 0
    level 5,totola 22,split to 2 groups 11 11 0 0 0 0 0 0
    level 6,totola 10,split to 6 groups 2 2 2 2 1 1 0 0
    level 7,totola 1,split to 1 groups 1 0 0 0 0 0 0 0
    level 8,totola 0,split to 0 groups 0 0 0 0 0 0 0 0



    I am not sure what formula to use!! please help??
    I am not sure if this is even feasible with the layout I am presenting, please advise!!

    PS: I am using excel Microsoft 365
    PPS: English is not my first language so please excuse any misspellings/phrases.

    Thank you so so much in advance

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: number of equal parts in "n" number of groups in excel

    Try this in F2:

    =IF(OR(COLUMNS($E2:E2)>$C2,$C2=0),0,ROUNDUP(($B2-SUM($E2:E2))/($C2-COLUMNS($E2:E2)+1),0))

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: number of equal parts in "n" number of groups in excel

    You can see the formula in the attached file.

  4. #4
    Registered User
    Join Date
    03-01-2020
    Location
    ksu
    MS-Off Ver
    360
    Posts
    2

    Re: number of equal parts in "n" number of groups in excel

    Wow ...... thank so so so so much just what I needed!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF formula with logical " Less than Or Equal To" NOT WORKING (Data is week number)
    By zeko90 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2013, 02:51 PM
  2. Replies: 5
    Last Post: 06-27-2012, 10:45 AM
  3. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  4. [SOLVED] Re: macro for converting number stored as "text" (or preceeded with ') to "number" formatting
    By markx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2006, 07:14 AM
  5. [SOLVED] macro for converting number stored as "text" (or preceeded with ') to "number" formatting
    By markx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2006, 10:20 AM
  6. How can I round a number to the nearest "50" or "100" in excel 200
    By moi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  7. How can I round a number to the nearest "50" or "100" in excel 200
    By Manny in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  8. [SOLVED] convert a number in Excel from numeric to text, i.e. "1" to "one"
    By buenavisionpaul in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1