Atoumate the counting of Net Finance

[alanas]

I've a worksheet on Excel 2003 with table like below:

Incure Debt Date | Debt amount | Payment of Debt Date | Net
Financing
10/11/2007 1000 11/1/2007 =????
10/18/2007 2000 11/5/2007
10/25/2007 2000 11/15/2007
11/1/2007 2500 11/22/2007
11/8/2007 3000 12/1/2007
11/15/2007 100 12/9/2007
11/22/2007 5000 12/15/2007
12/1/2007 5200 12/22/2007
12/9/2007 2000 12/23/2007
12/15/2007 2500 12/24/2007
12/22/2007 3000 12/25/2007
--------------------------------------------------------------------------------------
Here, Net financing for 11/22/2007 is equal to: 5000 (Debt amount incured on
11/22/2007) - 2500 (Debt amount that must be payed on 11/22/2007),
so 5000 - 2500 = 2500 (Net Financing on 11/22/2007).

The question is, How I can automate it? Is there any possibility to do it
using PivotTables and avoid VBA programming? Of course I could create a
formula and copy it everywhere I need, but it would take time. If it is
solved only using VB, so how?
thanks a lot.

 

[SeanC UK]

Hi Alanas,

You can do this using simple spreadsheet formulae in the cells. However,
there are not always dates in your example that match.

I have assumed that you have used columns A, B and C for the data you gave,
and have added two more columns, one for all dates, and one for the result of
Incrued Amount - Payment Amount.

(I hope the formatting remains in the following)

A B C D E
11/10/2007 1000 01/11/2007 11/10/2007 1000.00
18/10/2007 2000 05/11/2007 18/10/2007 2000.00
25/10/2007 2000 15/11/2007 25/10/2007 2000.00
01/11/2007 2500 22/11/2007 01/11/2007 1500.00
08/11/2007 3000 01/12/2007 05/11/2007 -2000.00
15/11/2007 100 09/12/2007 08/11/2007 3000.00
22/11/2007 5000 15/12/2007 15/11/2007 -1900.00
01/12/2007 5200 22/12/2007 22/11/2007 2500.00
09/12/2007 2000 23/12/2007 01/12/2007 2200.00
15/12/2007 2500 24/12/2007 09/12/2007 1900.00
22/12/2007 3000 25/12/2007 15/12/2007 -2500.00
22/12/2007 -2200.00
23/12/2007 -2000.00
24/12/2007 -2500.00
25/12/2007 -3000.00

In column E I have used a formula to look up dates and find the right
values. The formula in Cell E1 is:

=IF(ISERROR(MATCH(D1,A:A,0)),0,INDEX(B:B,MATCH(D1,A:A,0)))-IF(ISERROR(MATCH(D1,C:C,0)),0,INDEX(B:B,MATCH(D1,C:C,0)))

This will hopefully give you an idea of how to do what you require, if it
doesn't solve it for you.

I hope this helps.

Sean.