Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group data by UK financial years
Hi Group,
I have hundreds of rows of data. I would like to group the data by UK financial year (01 Apil - 31 Mar) to obtain totals by releavnt years. Can someone suggest a way forward? I have tried pivot tables but Excel says it cannot group on that data. Ideally, I would like to end up with something like the following: Year Total Payments 01/04/2002 - 31/03/2003 £79,557.25 01/04/2003 - 31/03/2004 £244,728.69 01/04/2004 - 31/03/2005 £450,063.19 01/04/2005 - 31/03/2006 £5,000.00 Many thanks Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group data by UK financial years
Tony,
Take a look at the function SumProduct(). It will do the job ... HTH Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group data by UK financial years
Carim,
Not so sure. Dates are spread across 10 different years, with different dates and months etc. WOuld I not need a function to group the financial year first and then use sumproduct form there? I'm not an expert but after reading previous posts grouping dates by financial years seems pretty daunting Thanks Tony |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group data by UK financial years
Tony,
Yes. You are right. My understanding was that you had already 3 columns : Beg Date, End Date and Amount ... Cheers Carim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group data by UK financial years
Tony,
As far Fiscal Years are concerned, you could adapt your own UDF based on the following one : Function FY(dDate As Date) As String Select Case Month(dDate) Case 6, 7, 8: FY = "Q1 " & "FY" & Year(dDate) + 1 Case 9, 10, 11: FY = "Q2 " & "FY" & Year(dDate) + 1 Case 12: FY = "Q3 " & "FY" & Year(dDate) + 1 Case 1, 2: FY = "Q3 " & "FY" & Year(dDate) Case 3, 4, 5: FY = "Q4 " & "FY" & Year(dDate) End Select End Function Hope this clarifies Cheers Carim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy drag financial years | Excel Worksheet Functions | |||
Copy Paste Financial Years | Excel Worksheet Functions | |||
Group Excel data in varying length financial periods 4/5 weeks | Excel Discussion (Misc queries) | |||
Financial Years | Excel Discussion (Misc queries) | |||
year function and financial years | Excel Worksheet Functions |