Analysing data imported from MS Money

[martin_pentreath]

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

 

[AltaEgo]

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.

 

[pub]

(e-mail address removed) wrote in (e-mail address removed):

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

 

[martin_pentreath]

(e-mail address removed) wrote in (e-mail address removed):












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

 

[Pete_UK]

Martin,

take a look here for an in-depth explanation of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete

 

[martin_pentreath]

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.

 

[AltaEgo]

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.