+ Reply to Thread
Results 1 to 5 of 5

Deleting units allocated in a range of cells based on the cell rank

  1. 11-06-2014, 11:37 PM #1
    LBala
    LBala is offline
    Registered User
    Join Date
    04-03-2014
    Location
    Dallas, USA
    MS-Off Ver
    Excel 2003
    Posts
    14

    Deleting units allocated in a range of cells based on the cell rank

    I will try to explain the best way I can, the attached table makes it easier to visualize.

    I have 10 buckets that I am naming from A to J (bucket A, bucket B, etc). Each bucket is ranked from #1 to #10, #1 being the most important bucket, #10 the least important one. Each bucket has 0, 1, 2 or 3 balls inside. There are a total of 10 balls all together inside the buckets. The table shows the number of balls each bucket has.

    I need to take 3 balls out of the buckets beginning with the least important buckets first. Is there a formula I can enter in each cell highlighted in yellow to calculate the number of balls each cell should have at the end to accomplish a total of 7 balls inside the buckets?

    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-06-2014, 11:56 PM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Deleting units allocated in a range of cells based on the cell rank

    Sort by importance, then ...

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    2
    Bucket name:
    D
    B
    A
    E
    J
    G
    H
    C
    F
    I
    3
    Bucket rank#:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Total
    4
    Number of balls inside:
    1
    1
    2
    1
    1
    3
    0
    0
    0
    1
    10
    5
    Remove
    6
    Formula:
    1
    1
    2
    1
    1
    2
    0
    0
    0
    0
    2
    C6: =MIN(C4, $M$4 - $M$6 - SUM($B6:B6))
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 11-07-2014, 12:04 AM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Deleting units allocated in a range of cells based on the cell rank

    Or, preserving the original order,

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    2
    Bucket name:
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    3
    Bucket rank#:
    3
    2
    8
    1
    4
    9
    6
    7
    10
    5
    Total
    4
    Number of balls inside:
    2
    1
    0
    1
    1
    0
    3
    0
    1
    1
    10
    5
    Remove
    6
    Formula:
    2
    1
    0
    1
    1
    0
    1
    0
    0
    1
    3
    		 C6: =MAX(0, MIN(C4, $M$4 - $M$6 - SUMIF($C$3:$L$3, "<" & C3, $C$4:$L$4)))
    Reply With QuoteRegister To Reply
  4. 11-07-2014, 12:18 AM #4
    LBala
    LBala is offline
    Registered User
    Join Date
    04-03-2014
    Location
    Dallas, USA
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Deleting units allocated in a range of cells based on the cell rank

    Absolutely perfect! That's exactly the formula I was looking for.
    Thanks.
    Reply With QuoteRegister To Reply
  5. 11-07-2014, 12:36 AM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Deleting units allocated in a range of cells based on the cell rank

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Deleting a range of cells based on a deletion of a cell outside that range
    By Carlos90 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 03:07 PM
  2. Deleting data in a range of cells based on if there is data in a cell outside the range
    By Carlos90 in forum Excel General
    Replies: 9
    Last Post: 08-14-2013, 12:10 AM
  3. [SOLVED] Trouble deleting entire row fr a range based on text in one cell & value of 0 in another
    By kmarshall6576 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2013, 11:36 AM
  4. Deleting the units (letters) in two cell ranges and graph it
    By dami in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2009, 03:34 PM
  5. Selecting and deleting named range based on cell value
    By Rich Kniatt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2006, 03:04 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1