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

[TABLE="width: 263"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Value Date
[/TD]
[TD]Amt[/TD]
[TD]Cumm Total[/TD]
[TD]Days O/S
[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2017[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]12/23/2017[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]960[/TD]
[TD="align: right"]14
[/TD]
[/TR]
[TR]
[TD="align: right"]12/24/2017[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]1450[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]12/25/2017[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]1940[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 263"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]A/C No.
[/TD]
[TD]Amt
[/TD]
[TD]Cumm Total
[/TD]
[TD]Days O/S
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]13
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]960[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]1440[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]490[/TD]
[TD="align: right"]980[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]52[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

9​

[/td][td]A/C No.[/td][td]Amt[/td][td]Cumm Total[/td][td]Days O/S[/td][td][/td][td]

1140​

[/td][td]a[/td][td]

1140​

[/td][td="bgcolor:#F8CBAD"]

15​

[/td][/tr]
[tr][td]

10​

[/td][td]A[/td][td]

480​

[/td][td]

480​

[/td][td]

13​

[/td][td]

​

[/td][td]

500​

[/td][td]b[/td][td]

500​

[/td][td="bgcolor:#F8CBAD"]

2​

[/td][/tr]
[tr][td]

11​

[/td][td]A[/td][td]

480​

[/td][td]

960​

[/td][td]

14​

[/td][td]

​

[/td][td]

1000​

[/td][td]c[/td][td]

1000​

[/td][td="bgcolor:#F8CBAD"]

51​

[/td][/tr]
[tr][td]

12​

[/td][td]A[/td][td]

480​

[/td][td]

1440​

[/td][td]

15​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]B[/td][td]

490​

[/td][td]

490​

[/td][td]

1​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]B[/td][td]

490​

[/td][td]

980​

[/td][td]

2​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]C[/td][td]

500​

[/td][td]

500​

[/td][td]

50​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]C[/td][td]

500​

[/td][td]

1000​

[/td][td]

51​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]C[/td][td]

500​

[/td][td]

1500​

[/td][td]

52​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]A/C No.[/TD]
[TD]Amt[/TD]
[TD]Cumm Total[/TD]
[TD]Days O/S[/TD]
[TD][/TD]
[TD]

1140​

[/TD]
[TD]a[/TD]
[TD]

1140​

[/TD]
[TD="bgcolor: #F8CBAD"]

15​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]A[/TD]
[TD]

480​

[/TD]
[TD]

480​

[/TD]
[TD]

13​

[/TD]
[TD][/TD]
[TD]

500​

[/TD]
[TD]b[/TD]
[TD]

500​

[/TD]
[TD="bgcolor: #F8CBAD"]

2​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]A[/TD]
[TD]

480​

[/TD]
[TD]

960​

[/TD]
[TD]

14​

[/TD]
[TD][/TD]
[TD]

1000​

[/TD]
[TD]c[/TD]
[TD]

1000​

[/TD]
[TD="bgcolor: #F8CBAD"]

51​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]A[/TD]
[TD]

480​

[/TD]
[TD]

1440​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]B[/TD]
[TD]

490​

[/TD]
[TD]

490​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]B[/TD]
[TD]

490​

[/TD]
[TD]

980​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]C[/TD]
[TD]

500​

[/TD]
[TD]

500​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]C[/TD]
[TD]

500​

[/TD]
[TD]

1000​

[/TD]
[TD]

51​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]C[/TD]
[TD]

500​

[/TD]
[TD]

1500​

[/TD]
[TD]

52​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.

50 instead of 2

#REF! instead of 52

<tbody>
[TD="class: xl67"]1441
[/TD]
[TD="class: xl68, width: 64"]a
[/TD]
[TD="class: xl66, width: 64"]1441
[/TD]
[TD="width: 148"]1 instead of 15
[/TD]

[TD="class: xl66, width: 64"]5001
[/TD]
[TD="class: xl65, width: 64"]b[/TD]
[TD="class: xl66, width: 64"]5001[/TD]

[TD="class: xl66, width: 64"]1501[/TD]
[TD="class: xl65, width: 64"]c[/TD]
[TD="class: xl66, width: 64"]1501[/TD]

</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.

50 instead of 2

#REF! instead of 52

<tbody>
[TD="class: xl67"]1441[/TD]
[TD="class: xl68"]a[/TD]
[TD="class: xl66, width: 64"]1441[/TD]
[TD="width: 148"]1 instead of 15[/TD]

[TD="class: xl66, width: 64"]5001[/TD]
[TD="class: xl65, width: 64"]b[/TD]
[TD="class: xl66, width: 64"]5001[/TD]

[TD="class: xl66, width: 64"]1501[/TD]
[TD="class: xl65, width: 64"]c[/TD]
[TD="class: xl66, width: 64"]1501[/TD]

</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)))