Finding due date for a list of accounts based on their respective approximate cummulative total

[ssanjy]

I need a help in excel in finding due date for a list of accounts based on their respective approximate cummulative total. Below is

1. Destination sheet has account wise receivable balance
2. Source sheet has date wise interest accrual (denoted by Tran Type C)and interest payment (denoted by TranType D)
3. Days O/S is the difference between the valur date of Interest Accrual and Date of Reporting
4. Need to lookup the Days O/S of each account from the Source sheet based on the due amount in Destination Sheet to match (exact or approximate) the corresponding O/S Days of the cummulative receivable of Tran Type C
5. If the lookup amount is even a fraction greater than the corresponding cummulative amount then it should fetch next O/S Days.
eg

Value Date	Amt	Cumm Total	Days O/S
12/22/2017	480	480	13
12/23/2017	480	960	14
12/24/2017	490	1450	15
12/25/2017	490	1940	16

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

In above table:
If the lookup amount is 14.00, it should return 13
If the lookup amount is 1452.33, it should return 16 instead of 15

Since I am not allowed to post attachment, please find the sample file in following google drive link

https://drive.google.com/open?id=1pZ-N1_YTPhVeiOOv0b6f7sMkWwcz1OB-

 

[ssanjy]

I did not know we cannot post attachments and should use other methods instead. But I cannot access the other methods from my office network. I'll update the post once I get to access internet from my home. Please excuse me till then.

 

[Aladin Akyurek]

Looks like:

=INDEX($D$2:$D$5,MATCH(MAX(F1,MIN($C$2:$C$5)),$C$2:$C$5,1)+(LOOKUP(MAX(F1,MIN($C$2:$C$5)),$C$2:$C$5) < MAX(F1,MIN($C$2:$C$5))))

where F1 = 1452.33.

 

[ssanjy]

Looks like:

=INDEX($D$2:$D$5,MATCH(MAX(F1,MIN($C$2:$C$5)),$C$2:$C$5,1)+(LOOKUP(MAX(F1,MIN($C$2:$C$5)),$C$2:$C$5) < MAX(F1,MIN($C$2:$C$5))))

where F1 = 1452.33.

Thanks a lot for the suggestions. I'll try to implement in my main file.

 

[ssanjy]

Looks like:

=INDEX($D$2:$D$5,MATCH(MAX(F1,MIN($C$2:$C$5)),$C$2:$C$5,1)+(LOOKUP(MAX(F1,MIN($C$2:$C$5)),$C$2:$C$5) < MAX(F1,MIN($C$2:$C$5))))

where F1 = 1452.33.

I needed a conditional lookup to match account numbers as well. So I simply added conditional MIN and lookup range. Here's what I did.

=INDEX($E$10:$E$17,MATCH(MAX(G9,MIN(IF($B$10:$B$17=$H$9,$D$10:$D$17))),IF($B$10:$B$17=$H$9,$D$10:$D$17),1)+
(IF(G9>MAX(IF($B$10:$B$17=$H$9,$D$10:$D$17)),0,LOOKUP(MAX(G9,MIN($D$10:$D$17)),IF($B$10:$B$17=$H$9,$D$10:$D$17))<MAX(G9,MIN(IF($B$10:$B$17=$H$9,$D$10:$D$17))))))


Where G9 is the lookup amount and H9 is the a/c number in below table.

A/C No.	Amt	Cumm Total	Days O/S
A	480	480	13
A	480	960	14
A	480	1440	15
B	490	490	1
B	490	980	2
C	500	500	50
C	500	1000	51
C	500	1500	52

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Can the above formula be trimmed to be more efficient as this array formula is time consuming for my large data set.

 

[Aladin Akyurek]

Row\Col	B​	C​	D​	E​	F​	G​	H​	I​	J​
9​	A/C No.	Amt	Cumm Total	Days O/S		1140​	a	1140​	15​
10​	A	480​	480​	13​	​	500​	b	500​	2​
11​	A	480​	960​	14​	​	1000​	c	1000​	51​
12​	A	480​	1440​	15​	​
13​	B	490​	490​	1​	​
14​	B	490​	980​	2​	​
15​	C	500​	500​	50​	​
16​	C	500​	1000​	51​	​
17​	C	500​	1500​	52​	​

In I9 control+shift+enter, not just enter, and copy down:

=MAX(G9,MIN(IF($B$10:$B$17=H9,$D$10:$D$17)))

In J9 control+shift+enter, not just enter, and copy down:

=INDEX($E$10:$E$17,MATCH(I9,IF($B$10:$B$17=H9,$D$10:$D$17),1)+(LOOKUP(I9,IF($B$10:$B$17=H9,$D$10:$D$17)) < I9))

Does the foregoing set up deliver an admissible/satisfactory performance?

 

[ssanjy]

Row\Col	B​	C​	D​	E​	F​	G​	H​	I​	J​
9​	A/C No.	Amt	Cumm Total	Days O/S		1140​	a	1140​	15​
10​	A	480​	480​	13​		500​	b	500​	2​
11​	A	480​	960​	14​		1000​	c	1000​	51​
12​	A	480​	1440​	15​
13​	B	490​	490​	1​
14​	B	490​	980​	2​
15​	C	500​	500​	50​
16​	C	500​	1000​	51​
17​	C	500​	1500​	52​

<tbody>
</tbody>

In I9 control+shift+enter, not just enter, and copy down:

=MAX(G9,MIN(IF($B$10:$B$17=H9,$D$10:$D$17)))

In J9 control+shift+enter, not just enter, and copy down:

=INDEX($E$10:$E$17,MATCH(I9,IF($B$10:$B$17=H9,$D$10:$D$17),1)+(LOOKUP(I9,IF($B$10:$B$17=H9,$D$10:$D$17)) < I9))

Does the foregoing set up deliver an admissible/satisfactory performance?

Thank you for your tip but the formula is not working where the max value of an A/C exceeds its corresponding cumm. value. eg in your above table following is observed.

1441	a	1441	1 instead of 15
5001	b	5001	50 instead of 2
1501	c	1501	#REF! instead of 52

<tbody>
</tbody>

 

[Aladin Akyurek]

Thank you for your tip but the formula is not working where the max value of an A/C exceeds its corresponding cumm. value. eg in your above table following is observed.

1441	a	1441	1 instead of 15
5001	b	5001	50 instead of 2
1501	c	1501	#REF! instead of 52

<tbody>
</tbody>

An issue of specification...

In I9 control+shift+enter, not just enter, and copy down:

=MAX(MIN(G9,MAX(IF($B$10:$B$17=H9,$D$10:$D$17))),MIN(IF($B$10:$B$17=H9,$D$10:$D$17)))