pivot calculation problem

  • Thread starter Thread starter =?iso-8859-1?Q?J=FCrgen?= Reinecke
  • Start date Start date
?

=?iso-8859-1?Q?J=FCrgen?= Reinecke

Hi Folks,


sorry if that's a FAQ or so, but I couldn't find something about
that anywhere in the net - neither microsoft support nor google
revealed something to solve my problem. I am using
MS Excel2000 (9.0.2812) on Windows XP.

I have a simple budget plan listing (fake) household
expenses per month, broken down into some categories. The
data looks like

Year Month Living Mobilty Hobby other
---------------------------------------------
2000 Jan 300 200 322 452
2000 Feb 312 223 300 123
.... ... ... ... ... ...
2000 Dec ... ... ... ...
2001 Jan ... ... ... ...
.... ... ... ... ... ...
2001 Dec ... ... ... ...
2002 Jan
.... ... ... ... ... ... ...
etc. etc.

I then create a pivot table with Year and Month as
Page-Values, and the categories as Row-Values (no columns).
As long as I use 'standard' as data format that works fine and
reveals a table like that:

|Year 2000
|Month Feb
|
|sum living 312
|sum mobility 223
|sum hobbies 300
|sum other 123

However, I want to see e. g. the difference between expenses
for hobbies and living, say in Feb 2000. I therefore
- click on the hobbies-field,
- select 'field properties' and then 'options'

then:
- display data as : difference of
- basis field : living
- basis element : 312

Alas then I run into a #NV-error, that instead of my desired
difference excel displays #NV. Question is: why, and how do I
arrive at my result? Is there any tutorial available on the
internet?

Thanks in advance


Jürgen
 
Your budget is set up to resemble a pivot table, so you should be able
to do the calculations in an adjacent column, without creating a pivot
table.

A pivot table is helpful when the date is listed vertically, e.g.:

Year Month ExpType Amt
2000 Jan Living 300
2000 Jan Mobility 200
 
Debra said:
Your budget is set up to resemble a pivot table, so you should
be able to do the calculations in an adjacent column, without
creating a pivot table.
Click to expand...
Definitely so, but I wanted to get familiar with the benefits
of a pivot table (I want to use that as example during a
lecture where pivot-tables are an issue).

I created a new table according to your suggestion
Year Month ExpType Amt
2000 Jan Living 300
2000 Jan Mobility 200
2000 Jan Misc 115
2000 Feb Living 300
... ... ... ...
2000 Dec ... ...
2001 Jan Living 325
... ... ... ...
Click to expand...

and put Year as page, Month as Row, ExpType as Column
and Amt as Data, which at least healed the '#NV'-error,
I now do get numbers when I change the display from 'standard'
to 'difference from' (alas the numbers don't match my
expectations, but I am at least one step further now).

So I understand it's the table layout (what is rows, columns and
data) is of great importance, and to perform calculations the data
has to be arranged properly.

I'll have a look at your website anyway.
Thanks so far


Jürgen
 
Back
Top