Pivot Table Mystery ... v.2

Y

York

Hi,
I have a Pivot Table Mystery ...
Maybe someone can help me solve it.
It's a bit difficult to explain ..
(This is my second attempt !)
So I have posted a hardcopy of the screen to here
www.fransysco.net/PivotTableMystery.gif

The problem starts with the Messages field.
The number of Messages is in every line of data : 1064 even though the 1064
messages were only sent once. On the other hand, the replies are coming back
on a year after year basis : 42 in 2005, and 3 in 2006.

So to avoid the 1064 becoming summed I don't use Sum of messages, I use Max.
Bu this little trick seems to be causing problems in the Total column where
the
Replies to Messages ratio is coming out wrong at 2.11% instead of 4.23%.

Any idea on how I should sort that out ?

TIA

York.
 
D

Debra Dalgleish

Add a column to the source data, and calculate the reply ratio. Add that
column to the pivot table, and the grand total will correctly show the sum.
 
Y

York

Well thanks, I was thinking I would have to do that.
Any ideas of managing it on the Excel side ?

York.
 
Y

York

My data is being provided by another system and I will have to go and recode
the prog that pumps out the data. I was looking for a quick fix and wondeing
if there was something smart I had missed on the Excel side.
I will recode the prog that creates my data file. No big deal.

I see that Debra has a book out on Pivot Tables.
I'm glad about that. Most people I know are are frightened by them.

I (only) read through the Table of contents.
Maybe there is one thing missing (maybe it's in there somewhere) and that is
the ease with which Excel PTT can handle quite large raw ascii (csv or tab)
data files coming from DBMS on Windows or non-Windows systems (as "external
data").
People need convincing that flat ascii files are a bridge and Excel PT is
tha key to making sense of the data in the flat files.

I have talked with programmers who spend hours and hours doing reports by
hand every week or month when the DBMS they are using can very easily be
programmed to chuck out csv ot tab files every night. (Others are using ODBC
to connect live. But it is just soooo labooooorious and sloooow.)

If need be, these flat ascii files can be transferred to the client side by
scheduled FTP (that's easy too). And and Excel PT just reads in the
external data. I save my sheets as templates and that way users don't do any
harm to the PT structure. And the data is re-read every time the file is
opened. That's an Excel PT option.

I showed this to one programmer and we set up a test file as a proof of
concept on her system .
It took 15 minutes. They had Excel already installed on all the client
sations. She was amazed and then when she thought back on all those
month-end Fridays she had stay on at ther desk and pump out reports into the
middle of the night when every one had gone home, she just cried.

I have seen the Office 12 Excel Beta and there the PT are unlimited in size.
The "too many lines or columns" memory problems seem to have been resolved
too.


Is there a French version of the Recipe Book ?

HTH

York
 
Top