Strange Pivot Behavior

  • Thread starter Thread starter Lloyd H. London
  • Start date Start date
L

Lloyd H. London

I read a post regarding the identical problem that I have, but the
possible solution does not work for me:

I have an Excel 2002 datalist, with many columns. The fields of
importance for my purposes are
Client(A1) Purchase Price(E1) Paid Amout(F1) Balance Due(G1)

Client records repeat innumerable times, with varying Purchase Price and
Paid Amounts.
The Balance Due is a calculated field =Purchase Price - Paid Amount
=E2-F2 in G2 and is filled down. The calculation yields appropriate
and correct amounts with the three ranges formatted with the Accounting
format.
I then summarize the data using a PivotReport . The problem is that
when the Purchase Price and the Paid Amount are dropped into the Data
area of the Pivot Layout screen, they default to Count rather than
Sum. The Balance Due, on the other hand defaults to the Sum function.
I have checked and recheck the formatting, it is set to Accounting.
I made a copy of the file and "played" with formatting for the two
misbehaving fields, to no avail.
I m at a lose to explain or correct the problem. Any suggestions would
be greatly appreciated.
 
Setting the column's formatting to Accounting won't affect the default
behaviour of the pivot table. If there are blank cells, or cells with
text, in the column, Excel will default to the Count function when the
field is added to the data area.

If the column contains only numbers, it should default to Sum.
 
Blank cells is the answer. Many thanks and seasons greeting.
And thanks for all the gems you and your collegues post. I learn a
tremendous amout just lurking.
 
You're welcome, and thanks for the feedback. There's always something
interesting to learn here!
 
Back
Top