Loans File; attempt to track a loan's balance at one year prior to another's commencement

An update of this situation. One finds the following response.




I had set up the file so that one column featured




Prior Loans




This section presented the number of loans preceding the loan logged in the present loan for the row. So, the first loan lacked preceding loans, so 0.




Another row featured the row value of the current row. So, the row containing the first loan featured a row value of 2 (this occurred as the first row in the sheet featured labels, hence displacing by one the data rows).




For this example, mentally construct five loans for Ms. Margo L--- and three loans for Ms. Myra R---




So, to present this situation visually:




Prior Loans Row Value Prior Loans-Row Value

0 2 2

1 3 2

2 4 2

3 5 2

4 6 2

0 7 7

1 8 7

2 9 7

3 10 7




Therefore, I determined that Prior Loans-Row Value will always equal the row value of the first row to feature a respective participant's first loan.




So, to assure that I will always arrive at the start of the loan range for the respective participant, one constructs the following formula:




=ADDRESS(ROW(I2-F2,9,2,1)




Perhaps also:




=ADDRESS(ROW($I$1)-1+H2-F2,9,2,1)




One can use the Offset function to travel around using this formula. The second loan always occurs, perforce, in the second row of the respective participant's list of loans. The Offset functions allows one to retrieve adjacent data, horizontally (C) or vertically (R).




The Indirect function served quite helpfully in this situation, allowing one to retrieve data from the address function.

I have an update on this situation.

I contemplated summing the balance left on all prior loans when a subsequent loan occurs. The columns of data appear as follows.

Balance left on first loan when second loan commenced
Balance left on first loan when third loan commenced; Set as First Column
Balance left on first loan when fourth loan commenced; Set as Second Column
Balance left on first loan when fifth loan commenced; Set as Third Column
Balance left on second loan when third loan commenced; Set as Fourth Column
Balance left on second loan when fourth loan commenced; Set as Fifth Column
Balance left on second loan when fifth loan commenced; Set as Sixth Column
Balance left on third loan when fourth loan commenced; Set as Seventh Column
Balance left on third loan when fifth loan commenced; Set as Eighth Column
Balance left on fourth loan when fifth loan commenced; Set as Ninth Column

Some guides:
The first loan precedes four loans
The second loan precedes three loans and follows one loan
The third loan precedes two loans and follows two loans
The fourth loan precedes one loan and follows three loans

One would add up data as follows:

Balance left on first loan when third loan commenced+Balance left on second loan when third loan commenced; so C1+C4

Balance left on first loan when fourth loan commenced+Balance left on second loan when fourth loan commenced+Balance left on third loan when fourth loan commenced; so as C2+C5+C7

Balance left on first loan when fifth loan commenced+Balance left on second loan when fifth loan commenced+ Balance left on third loan when fifth loan commenced+Balance left on fourth loan when fifth loan commenced; so as C3+C6+C8+C9

The number pattern seems as follows:
Offset 3
Offset 3, 2
Offset 3, 2, 1

3-0=3
3-1=2
2+1=3
3-1=2
2-1=1

I sought to try to find a way to automate the relations amongst numbers.