# Pivot Table - sum AND average in the same field

#### velocitygrrl

##### New Member
Hi there,

I am SURE this must be possible, but I can't for the life of me make it work.

I have a massive pivot table with three different categories of source data listed and subtotaled by a few different fields. Of the three categories of source data, I want two of them to SUM in the subtotals and one of them to AVERAGE.

I know I can change these settings under "Field Settings" but every time I do so, it wants to change all three to SUM, all three to AVERAGE, or all three to SUM and AVERAGE, but i can't seem to sum two and average one, nor can I hide just the subtotals I don't need.

I hope someone can make sense of this simple yet complicated question!!

Many thanks.
v.grrl.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Norie

##### Well-known Member
Can't you just add the data field again and then set average/sum/whatever?

#### velocitygrrl

##### New Member
I don't think I follow.

Of my three source categories, I can properly set each of them to either sum or average, but the subtotals/totals seem to only want to be one or the other..

#### Norie

##### Well-known Member

What I meant was that if you had a data item that was a sum then you could add that item again but set it to average.

#### velocitygrrl

##### New Member

yeah this is a tough one to explain. can i attach a screenshot somehow?

i have three fields across the top, and three types of data
it looks kinda like this:

field 1 field 2 field 3
number1 5
number2 100
% 5%
number1 1
number2 100
% 1%
subtotal field 3 number1 6
subtotal field 3 number2 200
subtotal field 3 % 6%

where you see number1 and number2, i am successfully showing a sum, where you see % i am successfully showing an average

subtotal field 3 is my problem -- i can make all three appear as a sum or an average, but can't show the number1 and number2 as sums and % as an average..

does this make any sense at all??
[/img]

#### Norie

##### Well-known Member
You can post an example of your data and required result using Colo's HTML Maker?

#### velocitygrrl

##### New Member
ok - it looks like some spaces are causing issues here, but hopefully it's enough to give you the idea. the bit highlighted in yellow is my trouble area..!
2005 Dec 13 - Ad Text Performance.xls
CDEF
4KeywordMatchingKeywordData12/1/05
6Clicks5
7CTR29.41%
8domainkeyImpressions3
9Clicks0
10CTR0.00%
11senderidImpressions16
12Clicks1
13CTR6.25%
14senderidImpressions6
15Clicks0
16CTR0.00%
Sheet1

#### Norie

##### Well-known Member
Can you repost after reading the FAQ regarding the HTML maker, the nbsp is a known problem?

Also it might be an idea if you posted an example of the raw data the pivot is based on.

That would give us something to work with.

Replies
0
Views
78
Replies
0
Views
86
Replies
0
Views
113
Replies
5
Views
39
Replies
5
Views
375