Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Atoumate the counting of Net Finance
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Atoumate the counting of Net Finance
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. -- (please remember to click yes if replies you receive are helpful to you) "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. If it is solved only using VB, so how? thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel and Finance | Excel Programming | |||
Excel and Finance | Excel Discussion (Misc queries) | |||
Finance Formula Help | Excel Worksheet Functions | |||
finance functions | Excel Programming | |||
For all you finance types... | Excel Discussion (Misc queries) |