Results 1 to 8 of 8

Thread: Subtracting from a total and show remaning

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Subtracting from a total and show remaning

    Hello,
    Ok so what I am trying to do is, I have a total of 500.
    And I have different accounts I want the 500 to come out of like for example:

    (A1) (B1) (C1)
    Dan. 100. ¤
    Bob. 50. ¤
    Mark. 50. ¤
    John. 25.
    Alex. 200. ¤
    Josh. 300. ¤

    TOTAL. REMAINING
    500.

    And from the total I need subtracted from accounts of my choosing.

    I have check boxes by each account so i can choose which accounts the 500 comes out of.
    Ok now below this on the same sheet I have the same accounts but what accounts i select on top effects the bottom like a before and after type scenario.
    Example,
    Say I use Dan (100), john (25), Alex(200) which equals 325, so remaning only 175.
    I then choose Josh last to cover the remainder. So the bottom would reflect as so. 300-175=125
    And I used all the numbers in Dan,John,alex so they are all depleted showing zeros.

    Dan. 0.
    Bob. 50.
    Mark. 50.
    John. 0.
    Alex. 0.
    Josh. 125.



    I've tried a bunch of things I could think of i keep either having numbers go negative so i use MAX,0 and tried to use MAX with IF statements and sumifs I just can not figure out the right combination to do this.

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Subtracting from a total and show remaning

    @mewingpants
    Maybe.....
    Excel 2010
    ABC
    1NameCheckbox
    2Dan100TRUE
    3Bob50FALSE
    4Mark50FALSE
    5John25TRUE
    6Alex200TRUE
    7Josh300TRUE
    8
    9TOTAL REMAINING
    10500
    11
    12
    13
    14NameBalance
    15Dan0
    16Bob50
    17Mark50
    18John0
    19Alex0
    20Josh125

    Sheet1



    Worksheet Formulas
    CellFormula
    B15=IF(A$10-SUMIF(C$2:C2,TRUE,B$2:B2)<0,MIN(B2,ABS(A$10-SUMIF(C$2:C2,TRUE,B$2:B2))),IF(C2,MAX(0,B2-SUM(B$2:B2)),B2))



    It looks a bit bulky but I did bolster it in case there are more than one selected names effectively with surplus.
    NB it only alculates top to bottom.

    Hope that helps.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting from a total and show remaning

    wow, amazing.... that works perfectly. Thank you so much, I have been messing around for hours trying to figure a way to do this.

  4. #4
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting from a total and show remaning

    Ok just one question though, so say the number for the TOTAL is met being 500, would their be a way to not subtract anymore number even if you select more check boxes than you need?

  5. #5
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Subtracting from a total and show remaning

    It does that now if the first selected value >= 500
    Do you mean if 500 for any selected value it should be used in preference?
    Can you illustrate that scenario?
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  6. #6
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting from a total and show remaning

    Not sure if I did something wrong but it works perfect when select dan,john,alex and josh to get the 500 and shows the remaining amount Josh has which is 125.
    Ok so the condition is met but say i also choose Bob and Mark, the bottom graph is turn Bob and Mark to zero if though the 500 was already reached like it resets maybe? Would their be a safety formula to prevent IF condition met (the 500) that it will not subtract anymore accounts if the checkbox is checked? Sorry if this is not clear it's just this chart is just a demo the real one has numbers into the hundred millions.

  7. #7
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Subtracting from a total and show remaning

    If you are wanting something other than what I have provided then I am afraid that it is not clear to me.
    My formula works top to bottom on the account list and allocates only for accounts that are checked, TRUE. Once the Total Remaining ammount has been allocated, it will not allocate frome names below, even if they are checked.
    For example...
    Excel 2010
    ABC
    1NameCheckbox
    2Dan100TRUE
    3Bob50TRUE
    4Mark50TRUE
    5John25TRUE
    6Alex200TRUE
    7Josh300TRUE
    8
    9TOTAL REMAINING
    10125
    11
    12
    13
    14NameBalance
    15Dan0
    16Bob25
    17Mark50
    18John25
    19Alex200
    20Josh300

    Sheet1



    Worksheet Formulas
    CellFormula
    B15=IF(A$10-SUMIF(C$2:C2,TRUE,B$2:B2)<0,MIN(B2,ABS(A$10-SUMIF(C$2:C2,TRUE,B$2:B2))),IF(C2,MAX(0,B2-SUM(B$2:B2)),B2))

    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  8. #8
    New Member
    Join Date
    Apr 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting from a total and show remaning

    Ok awesome that was exactly what I was trying to do. Once the total is met do not subtract from anymore cells even if it is marked true. Thanks for your time I'll look over the forumla's to understand more of how they work, still learning excel.


    Again thank you for all your help I really do appreciate it.
    Last edited by mewingpants; Aug 18th, 2019 at 06:39 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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