Pivot Table - sum AND average in the same field

velocitygrrl

New Member
Joined
Aug 10, 2005
Messages
14
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.
 

Some videos you may like

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
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Can't you just add the data field again and then set average/sum/whatever?
 

velocitygrrl

New Member
Joined
Aug 10, 2005
Messages
14
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
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Could you give more information about the data and what you want to do?

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
Joined
Aug 10, 2005
Messages
14

ADVERTISEMENT

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]
 

velocitygrrl

New Member
Joined
Aug 10, 2005
Messages
14
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
5BroadauthenticationmethodsImpressions17
6Clicks5
7CTR29.41%
8domainkeyImpressions3
9Clicks0
10CTR0.00%
11senderidImpressions16
12Clicks1
13CTR6.25%
14senderidImpressions6
15Clicks0
16CTR0.00%
17BroadSumofImpressions42
18BroadSumofClicks6
19BroadSumofCTR35.66%
Sheet1
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,384
Members
412,590
Latest member
Velly
Top