Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A slightly complicated finance problem
At school I have recieved a project where I have several seperate
workbooks with invoices for different products. These products have a purchase fee and an on-going subscription so there are invoices for different ranges which are payed over several months. I have to create a summary of all the data, making a table of all the different companies and their total(and product by product) invoices per financial year. The data is laid out as follows: Microsoft -------- Apple --------- Intel £51,205 -------- £23,504 --------- £82,512 21/3/2002 ------- 15/7/2003 -------- 5/6/2002 £13,214 ------- £34,312 -------- £9,312 15/1/2003 ------- 9/10/2003 -------- 2/8/2002 I started by trying to do it by brute force, i.e: =IF(AND('[WB1]Invoices'!C1037712,'[WB1]Invoices'!C10<38078),'[WB1] Invoices'!C9,"0") 37712 being the serial date for April 1 2003 and 38078 April 1 2004 But as I go through the list of tens and tens of different invoices I end up with huge lists functions in a single cell and I lose track of where I am. My question is if there is a more efficient way to do this? Thanks in advance for any help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A slightly complicated finance problem
You might consider a pivot table, which lends itself very well to financial
summaries by client, product, date, etc. Pivot tables even have built-in tools to summarize daily, monthly, quarterly, or annually for you. -- Please remember to indicate when the post is answered so others can benefit from it later. "TopCat" wrote: At school I have recieved a project where I have several seperate workbooks with invoices for different products. These products have a purchase fee and an on-going subscription so there are invoices for different ranges which are payed over several months. I have to create a summary of all the data, making a table of all the different companies and their total(and product by product) invoices per financial year. The data is laid out as follows: Microsoft -------- Apple --------- Intel £51,205 -------- £23,504 --------- £82,512 21/3/2002 ------- 15/7/2003 -------- 5/6/2002 £13,214 ------- £34,312 -------- £9,312 15/1/2003 ------- 9/10/2003 -------- 2/8/2002 I started by trying to do it by brute force, i.e: =IF(AND('[WB1]Invoices'!C1037712,'[WB1]Invoices'!C10<38078),'[WB1] Invoices'!C9,"0") 37712 being the serial date for April 1 2003 and 38078 April 1 2004 But as I go through the list of tens and tens of different invoices I end up with huge lists functions in a single cell and I lose track of where I am. My question is if there is a more efficient way to do this? Thanks in advance for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complicated problem I think Excel can handle | Excel Discussion (Misc queries) | |||
Slightly OT (Maybe) printing problem | Excel Worksheet Functions | |||
Complicated Problem | Excel Worksheet Functions | |||
Complicated Problem! | Excel Worksheet Functions | |||
Complicated Vlookup/count problem | Excel Worksheet Functions |