Thread: Subtracting from a total and show remaning Thanks:  1 Post #5327579 (1) Likes:  1 Post #5327579 (1)

1. 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.  Reply With Quote

2. Re: Subtracting from a total and show remaning

@mewingpants
Maybe.....
Excel 2010
ABC
1Name£Checkbox
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.  Reply With Quote

3. 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.  Reply With Quote

4. 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?  Reply With Quote

5. 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?  Reply With Quote

6. 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.  Reply With Quote

7. 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
1Name£Checkbox
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))  Reply With Quote

8. 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.  Reply With Quote

User Tag List

Tags for this Thread

accounts, dan, john, josh, total  Posting Permissions

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