Net Financing

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

 

[Joel]

I think the formula is the right method to use. I don't understand you
comment "everywhere I need". I would assume every row would need the
formula. I would think you would need to match the date in the Payment column
with the date in the incured column and then subtract the amount from the two
columns. I think a vlookup function is the only thing you need.

 

[Mike H.]

I think using the sumif() function would probably do what you want. Off to
the side, enter a formula like this:

sumif(A:A,e4,B:B)-sumif(C:C,e4,B:B) . If you enter that formula in f4 and
in e4 then just enter the date you want to view, 11/22/07. The answer will be
2500. This assumes your columns are in a-c. If you want to see this for
more dates, just enter a formula in e5 that is e4+1, etc, etc. Then copy the
formula in f4 down and you'll have a table of net activity on all dates.

 

[Ron Rosenfeld]

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.

It would probably take more time to update Pivot Tables.

Assuming that your "everywhere" is merely one column, and that your table is in
A1n, you could enter a formula of the type:

D2: =B2-SUMIF(C:C,A2,B:B)

and fill down as far as required.

If you don't like the 0's that result when there is no data, you could either
format 0 to not show, or wrap your formula in an IF statement to return a null
string

=IF(A2="","",B2-SUMIF(C:C,A2,B:B))




--ron

 

[alanas]

Thanks a lot for your answers,

Mike's answer really helped me. The formula:
sumif(A:A,e4,B:B)-sumif(C:C,e4,B:B)
that Mike suggested I adapted, like =B2-SUMIF(C:C,A2,B:B)
that is exactly the same as Ron suggested.
The point is that I was thinking on this formula nearly 2 hours, when there
was already an answer from Ron.

thanks to you all.