credit card conundrum

[nightster]

Hello everyone,

I’d really appreciate your help with this problem please…

We are managing our business spending across 4 credit cards.

Each credit card has a different limit. The total limit is £54,000

Every time one of the cards hits it’s credit limit, we move spending to the next card in the list.

Once the total spend gets to £53,000, we pay down the first card and start spending on that card again.

Card 1 has limit of £9,600. Card 2 has a limit of £14,400. Card 3 has a limit of £19,200. Card 4 has a limit of £10,800.

Please see this image to see how I would like this laid out: https://photos.app.goo.gl/g8aU7MRM2mH6Vvuq6

I would like to put the total spend for each week in row 2 and then for Excel to automatically fill in rows 5 to 8 and rows 11 to 14 (rows 17 to 20 and 23 to 26 are already taken care of as they are straight-forward).

Any ideas please community??? Thanks so much!

 

[nightster]

<d$2,if(d10>Hmm - Excel is saying that formula contains an error?</d$2,if(d10>

 

[AlanY]

it works for me

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12
2			£8,000	£10,000	£12,000	£10,000	£12,000	£18,000	£12,000	£20,000	£45,000	£10,000	£10,000	£10,000
3
4	Spend
5	Card 1	£9,600	£8,000	£1,600	£0	£0	£0	£9,600	£0	£0	£9,600	£0	£9,600	£0
6	Card 2	£14,400	£0	£8,400	£6,000	£0	£0	£8,400	£6,000	£0	£14,400	£0	£0	£10,000
7	Card 3	£19,200	£0	£0	£6,000	£10,000	£3,200	£0	£6,000	£13,200	£19,200	£0	£0	£0
8	Card 4	£10,800	£0	£0	£0	£0	£8,800	£0	£0	£6,800	£1,800	£10,000	£400	£0
9		£9,600
10	Repayments
11	Card 1			£0	£0	£0	£0	£9,600	£0	£0	£9,600	£0	£9,600	£0
12	Card 2			£0	£0	£0	£0	£14,400	£0	£0	£14,400	£0	£0	£14,400
13	Card 3			£0	£0	£0	£0	£0	£19,200	£0	£19,200	£0	£0	£0
14	Card 4			£0	£0	£0	£0	£0	£0	£10,800	£0	£10,800	£0	£0
Sheet1

Cell Formulas
Range	Formula
C5	=MIN($B5,C$2-SUM(C$4:C4))
D5	=MIN($B5-SUM($C5:C5)+SUM($C11:C11),D$2-SUM(D$4:D4))
D11	=IF(SUM($B$5:$B$8)+SUM($C$11:C$14)+SUM(D$10:D10)-SUM($C$5:C$8)D10>0,$B5,IF(AND(SUM($C$11:C$14)=0,$A11="Card 1"),$B5,IF($B5=IF(SUM(C$11:C$14)=0,0,LOOKUP(2,1/(C$11:C$14>0),$B$6:$B$9)),$B5,0))),0)

 

[nightster]

Thanks. That's strange. Any chance you could provide a download link again please?

(Ps. Is there anyway I can say "thank you"? Do you have a patreon for example?)

 

[AlanY]

here you're, have a look

https://drive.google.com/file/d/1C_R9AU32oXh6XvT_XO5VT44t-YaM9N9i/view?usp=sharing

 

[nightster]

It's still paying it down a week early? For example, card 1 is paid down in week 8 but not spent on again until week 9.

It should be paid down in week 9 and spent on again in week 9 also.

Does that make sense? Thanks!

 

[AlanY]

sorry, sent you the wrong link.
try again

https://drive.google.com/file/d/17NqV_Icm36VbfK-_-WXUQCqZZa59fcuk/view?usp=sharing