Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Analysing data imported from MS Money
Hi Excel experts,
I'm struggling with something that is probably quite simple. I've imported a whole load of financial transactions from MS Money. They're in a worksheet of their own called "Money data". Key data: Column B has date of transaction Column F has the amount Column H has the category into which the transaction falls Within the same workbook in a separate worksheet called "Annual figures" I want to have a table of total annual expenditure month-by- month from each category. Here are the columns I currently have in that worksheet. Column A: a list of all the categories Column B: I want this to contain total expenditure for the relevant category in the twelve months ending 31st Jan 08 Column C: I want this to contain total expenditure for the relevant category in the twelve months ending 29th Feb 08 Column D: etc, etc In row 4 of each column I have as a heading the month to which that column applies. It's displayed as eg "Jan-08", although the data within the cell is 01/01/2008 (ie the beginning of the month, not its end). I've been playing about with the "sumif" function, but I'm really struggling. Problems: - I'm not sure if I'm using 3D cell references correctly - I'm not sure how to express the first condition - the range of dates I'm summing within the formula and how to link it to the date in row 4 - Similarly I'm not sure how to have a second condition, ie for the figures to go into the sum, as well as being in the relevant 12-month period for that column they must fall into the category for that row. Any helpful suggestions and pointers gratefully received. Cheers! Martin |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Analysing data imported from MS Money
A pivot pivot table takes a lot of hard slog out of data summing. Once you
learn how to use them well, they give you a lot of flexibility and allow very fast analysis. Have a look at Debra Dalgleish's tips and techniques if you need instructions. http://www.contextures.com/tiptech.html Start looking under 'P' for pivot. -- Steve wrote in message ... Hi Excel experts, I'm struggling with something that is probably quite simple. I've imported a whole load of financial transactions from MS Money. They're in a worksheet of their own called "Money data". Key data: Column B has date of transaction Column F has the amount Column H has the category into which the transaction falls Within the same workbook in a separate worksheet called "Annual figures" I want to have a table of total annual expenditure month-by- month from each category. Here are the columns I currently have in that worksheet. Column A: a list of all the categories Column B: I want this to contain total expenditure for the relevant category in the twelve months ending 31st Jan 08 Column C: I want this to contain total expenditure for the relevant category in the twelve months ending 29th Feb 08 Column D: etc, etc In row 4 of each column I have as a heading the month to which that column applies. It's displayed as eg "Jan-08", although the data within the cell is 01/01/2008 (ie the beginning of the month, not its end). I've been playing about with the "sumif" function, but I'm really struggling. Problems: - I'm not sure if I'm using 3D cell references correctly - I'm not sure how to express the first condition - the range of dates I'm summing within the formula and how to link it to the date in row 4 - Similarly I'm not sure how to have a second condition, ie for the figures to go into the sum, as well as being in the relevant 12-month period for that column they must fall into the category for that row. Any helpful suggestions and pointers gratefully received. Cheers! Martin |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Analysing data imported from MS Money
On 1 Aug, 06:01, pub wrote:
wrote in news:d3565fa3-e92a-4636-a8e6- : Hi Excel experts, I'm struggling with something that is probably quite simple. I've imported a whole load of financial transactions from MS Money. They're in a worksheet of their own called "Money data". Key data: Column B has date of transaction Column F has the amount Column H has the category into which the transaction falls Within the same workbook in a separate worksheet called "Annual figures" I want to have a table of total annual expenditure month-by- month from each category. Here are the columns I currently have in that worksheet. Column A: a list of all the categories Column B: I want this to contain total expenditure for the relevant category in the twelve months ending 31st Jan 08 Column C: I want this to contain total expenditure for the relevant category in the twelve months ending 29th Feb 08 Column D: etc, etc In row 4 of each column I have as a heading the month to which that column applies. It's displayed as eg "Jan-08", although the data within the cell is 01/01/2008 (ie the beginning of the month, not its end). I've been playing about with the "sumif" function, but I'm really struggling. Problems: - I'm not sure if I'm using 3D cell references correctly - I'm not sure how to express the first condition - the range of dates I'm summing within the formula and how to link it to the date in row 4 - Similarly I'm not sure how to have a second condition, ie for the figures to go into the sum, as well as being in the relevant 12-month period for that column they must fall into the category for that row. Any helpful suggestions and pointers gratefully received. Cheers! Martin 1st some assumptions - i assumed your Money data sheet starts in Row 1 and ends in row 29 - Annual Figures cell A5 is categ1 - Annual figures cell B4 is 01/01/2008 then you can use the sumproduct formula in cell B5 =SUMPRODUCT(('Money data'!$B$1:$B$29=B$4)*('Money data'!$B$1:$B$29<=DATE (YEAR(B$4),MONTH(B$4)+1,1))*('Money data'!$H$1:$H$29=$A5)*('Money data'!$F $1:$F$29)) if its not working if you get an #N/A. i randomly assumed 29 rows. *remember all your ranges have to be the same number of rows. each section of the sumproduct should work independantly and give you a proper count. so a count of the categories (the 3rd section of the formula) would be =SUMPRODUCT(('Money data'!$H$1:$H$29=$A5)*1) the 4th section does the sum if you get it working on a smaller sheet, so you can easily count and change to test, then it should be easy to apply to your full sheet. good luck Hi Pub, It's taken me a couple of weeks to get round to implementing this, but just wanted to say a belated thanks for your suggestion, which I've got to work fine. Thanks for putting me onto the sumproduct function, which is ideal. One observation: the logical arguments don't work very well. For example the ('Money data'!$B$1:$B$29=B$4) bit produces 'FALSE' or 'TRUE' rather then 0 or 1, which screws the whole thing up. I had to coerce excel into giving me 1 or 0 by doing (0+('Money data'!$B$1:$B $29=B$4)) which seems to work. Also, I had to replace the asterisk characters with commas. Frankly I'm out on a limb here, knowing very little about Excel apart from absolute basics, so if the above is rubbish do please tell me, but it seems to work. Cheers! Martin |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Analysing data imported from MS Money
Martin,
take a look here for an in-depth explanation of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps. Pete On Aug 14, 4:18*pm, wrote: Hi Pub, It's taken me a couple of weeks to get round to implementing this, but just wanted to say a belated thanks for your suggestion, which I've got to work fine. Thanks for putting me onto the sumproduct function, which is ideal. One observation: the logical arguments don't work very well. For example the ('Money data'!$B$1:$B$29=B$4) bit produces 'FALSE' or 'TRUE' rather then 0 or 1, which screws the whole thing up. I had to coerce excel into giving me 1 or 0 by doing (0+('Money data'!$B$1:$B $29=B$4)) which seems to work. Also, I had to replace the asterisk characters with commas. Frankly I'm out on a limb here, knowing very little about Excel apart from absolute basics, so if the above is rubbish do please tell me, but it seems to work. Cheers! Martin |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Analysing data imported from MS Money
On 14 Aug, 16:25, Pete_UK wrote:
Martin, take a look here for an in-depth explanation of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps. Pete Thanks Pete, good page. For some reason I'm still not having any sucess in coercing Excel to treat the TRUE and FALSE arguments as 1 and 0 by using the asterisk operator rather then the comma. I just get a #VALUE error. However, adding zero is working, although it's not as neat, so I'm not too worried. |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Analysing data imported from MS Money
From Excel HELP on Value#:
"Microsoft Excel cannot translate the text into the correct data type. Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!." A simple exercise to show how Excel sees cells formatted as text differently from numbers: Enter '1 in cell A1 (with the single quote) Enter =A1=1 in B1 Enter =A1+0=1 in C1 Explanation: - The value you entered into A1 is text - Excel knows a text value does not equal a number - Performing a mathematical operation on a 'text' number changes it to a number. -- Steve wrote in message ... On 14 Aug, 16:25, Pete_UK wrote: Martin, take a look here for an in-depth explanation of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps. Pete Thanks Pete, good page. For some reason I'm still not having any sucess in coercing Excel to treat the TRUE and FALSE arguments as 1 and 0 by using the asterisk operator rather then the comma. I just get a #VALUE error. However, adding zero is working, although it's not as neat, so I'm not too worried. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Analysing my CSV data | Excel Discussion (Misc queries) | |||
Analysing data through pivot | Excel Discussion (Misc queries) | |||
analysing data from alternate columns using the countif function | Excel Discussion (Misc queries) |