Finding the first week when a whole value can be taken i.e. a credit is lower than the total invoice value by customer- MAX, MODE, INDEX

[lbanham]

Afternoon,

In the below example I am trying to work out in what week the credit can be taken, based on the total invoice value (by customer) exceeding the credit value.
I have tried variations of MODE, MAX and INDEX but no success.

any suggestions would be greatly received.

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G
1
2		Customer	Invoice	Due	Invoice or credit note		Week credit can be paid
3		a	100	Week 1	Invoice
4		b	200	Week 2	Invoice
5		a	300	Week 3	Invoice
6		b	100	Week 1	Invoice
7		c	200	Week 2	Invoice
8		d	300	Week 3	Invoice
9		a	100	Week 3	Invoice
10		a	250		Credit		Week 3
11		c	250		Credit		Future
12
Sheet10

Cell Formulas
Range	Formula
G3:G11	G3	=IF(E3="Credit",IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($D$2:$D2)/(SUMIFS(OFFSET(C$2,0,0,ROW(C$2:C2)-ROW(C$2)+1),OFFSET(B$2,0,0,ROW(B$2:B2)-ROW(B$2)+1),B3)>=C3),1)),"Future"),"")

If you have a mixture of credits and Invoices, then we'd need to modify the formula to account for prior credits.