Funds usage monitoring, weekly details

[Durendal]

Dear community,

I am coming to you with an excel problem that I am sure will challenge you. I am not sure what I expect is possible to compute though.



I am a reseller.

T buy what I sell, I send money to my purchasers in order for them to buy me some specific electronic components.

If I send him “1” of money, it means the money for one electronic component; when he purchases for 1, it means he bought one component (= all in the same unit).

I record weeks after weeks the amount of money I have send, and the purchases he has made. I then track the cumulative funding and purchase figures, for the specific weeks and as a cumulative.

The Outstanding equals to the cumulative funding minus the cumulative purchases (= the available money for him).

If I compute this data, here is how it would look (very simplified data):

I have compute what I am explaining in a file I put at your disposal.

What I would like to do is a summary of the number of weeks my purchaser has money at hand that he hasn’t converted to coco, but with further details. Let me explain.

If I use a count if formula of the all-time cumulative funding > to the current week cumulative purchase, (Column “Countif” in the file) I’ll get the amount of weeks since my purchaser has money that hasn’t been converted.

However, what I would like is to split the outstanding amount into the count if result I have. Let’s say my count if equals to 4, I would like to know which part of my outstanding comes from 4 weeks ago, which part come from 2 weeks, which part comes from the previous week…

I have a range of weeks I am interested in. I computed in the file the data you could use, and the expected result written manually.

I am not sure such a computation is possible on excel, however I require your excel brilliancy to crack this case, I don’t think it’s an easy one..

Thank you very much.

Also asked here Funds usage monitoring, weekly details
ans here Funds usage monitoring, weekly details

Reseller_Week_Count.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Data:
3
4			Week	Funding Weekly	Cumulative Funding	Purchases Weekly	Cum Purchases	Outstanding Money	Countif Formula:
5			1	1000	1000	900	900	100	1
6			2	1000	2000	200	1100	900	1
7			3	1000	3000	900	2000	1000	1
8			4	1000	4000	0	2000	2000	2
9			5	1000	5000	0	2000	3000	3
10			6	1000	6000	0	2000	4000	4
11			7	1000	7000	0	2000	5000	5
12			8	1000	8000	0	2000	6000	6
13			9	1000	9000	0	2000	7000	7
14			10	1000	10000	0	2000	8000	8
15			11	1000	11000	0	2000	9000	9
16
17									Control
18	EXPECTED RESULT:			Week⩽1	1<Weeks⩽2	2<Weeks⩽4	4<Weeks⩽8	Weeks>8	A	B	A=B
19			Week:	1	2	4	8	>8	SUM	Money Outstanding	Control
20			1	100					100	100	TRUE
21			2	900					900	900	TRUE
22			3	1000					1000	1000	TRUE
23			4	1000	1000				2000	2000	TRUE
24			5	1000	1000	1000			3000	3000	TRUE
25			6	1000	1000	2000			4000	4000	TRUE
26			7	1000	1000	2000	1000		5000	5000	TRUE
27			8	1000	1000	2000	2000		6000	6000	TRUE
28			9	1000	1000	2000	3000		7000	7000	TRUE
29			10	1000	1000	2000	4000		8000	8000	TRUE
30			11	1000	1000	2000	4000	1000	9000	9000	TRUE
31
Sheet1

Cell Formulas
Range	Formula
G5	G5	=F5
H5:H15	H5	=E5-G5
I5:I15	I5	=COUNTIF(E$5:E5,">"&G5)
G6:G15,E6:E15	G6	=G5+F6
E5	E5	=+D5
I20:I30	I20	=SUM(D20:H20)
J20:J30	J20	=H5
K20:K30	K20	=I20=J20
D20:D22	D20	=H5
D23	D23	=H8-H7