excel pivot tables

[sweetrats]

When using the pivot table wizard in excel, is there any way to select multiple button fields for data? Or, to reset the default function from sum to mean (eg)? I need to be able to get the means of (min) 50 different columns of data and selecting them one at a time and then having to convert from sum to mean for each one is less than optimally efficient. Any ideas? BTW - for the person who suggested using a macro for this, you can not access the macro record function while in the pivot table wizard.

 

[xlbo]

There is no way to change the defaults. They are set by data type. Measures get summed. Text gets counted

The only way to do it automatically is via code and you definitely can record it - you just have to start the macro recorder BEFORE you start the pivottable wizard

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Welcome to Tek-Tips. This is THE best place to grow your computer skills. It has been for me!

"I need to be able to get the means of (min) 50 different columns of data "

WOW! 50?

Could you post an example of your source data HEADINGS that you are aggregating?

BTW, the short answer is, record a macro to change ONE field. Post back in Forum707 to get help customizing.

But the long answer, might involve restructuring some data. Depends on your answer.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[sweetrats]

Here is an example;

sex subject image 1 image 2 image 3 image 4

I actually have data for 80 images by subject number and sex. BTW, these data are subject responses to the images in a psych experiment. In order to run the stats on the groups of individual images images, I need the means for each image by sex.
Does this make sense?

I will try to record the macro prior to starting the wizard to see if that helps.

Thanks Tek guys ;-)

 

[SkipVought]

Having Table Headings that contain DATA VALUES (Image1, Image2...) is NOT a best and accespted practice. This is know as Non-Normalized data.

Your table would be better, structured...
[tt]
sex subject ImageName
[/tt]
with a row for each image.

Then drag ImageName to the COLUMN area -- ONLY ONE field to format.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

You are actually better off creating your pivot table and leaving your measures as sums - then, once the table is in place, start recording a macro and right click on the pivottable, go to the wizard and change 1 field to Mean. Ok out of there and stop the recorder - that should give you your base code to go from

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

[tt]
sex subject ImageName ImageValue

[/tt]
need BOTH.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[sweetrats]

Thanks Skip (& Geoff)!!!

Seems doable.