Net Financing
 

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.

Net Financing
 

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.

"alanas" wrote:

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.

Net Financing
 

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.


"alanas" wrote:

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.

Net Financing
 

On Thu, 6 Dec 2007 05:14:03 -0800, alanas
wrote:

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
A1:Dn, 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

Net Financing
 

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.