Subtracting from a total and show remaning

[mewingpants]

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.

 

[Snakehips]

@mewingpants
Maybe.....

Excel 2010
	A	B	C
1	Name	£	Checkbox
2	Dan	100	TRUE
3	Bob	50	FALSE
4	Mark	50	FALSE
5	John	25	TRUE
6	Alex	200	TRUE
7	Josh	300	TRUE
8
9	TOTAL REMAINING
10	500
11
12
13
14	Name	Balance
15	Dan	0
16	Bob	50
17	Mark	50
18	John	0
19	Alex	0
20	Josh	125
Sheet1

Cell Formulas
Range	Formula
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.

 

[mewingpants]

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

 

[mewingpants]

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?

 

[Snakehips]

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?

 

[mewingpants]

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.

 

[Snakehips]

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
	A	B	C
1	Name	£	Checkbox
2	Dan	100	TRUE
3	Bob	50	TRUE
4	Mark	50	TRUE
5	John	25	TRUE
6	Alex	200	TRUE
7	Josh	300	TRUE
8
9	TOTAL REMAINING
10	125
11
12
13
14	Name	Balance
15	Dan	0
16	Bob	25
17	Mark	50
18	John	25
19	Alex	200
20	Josh	300
Sheet1

Cell Formulas
Range	Formula
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))

 

[mewingpants]

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.