Pivot Table- Calculated Field

C

chipseloy

New Member
Joined
Mar 25, 2009
Messages
1
I have a pivot table in which all Data Fields are summed except for one, which is Count (Claim Numbers). I am trying to add a calculated field that takes one of the summed fields (Sum of Capped T&D Incurred below)& divide it by the count field (Count of Claim Number) but it doesn't work & I'm not certain why (keeps assuming count is 1 for each period). Any Help is appreciated.
<TABLE style="WIDTH: 454pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=603 border=0 x:str><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" span=5 width=65><TBODY><TR style="HEIGHT: 45pt" height=60><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 57pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 45pt; BACKGROUND-COLOR: #333399" width=76 height=60>Count of Claim Number</TD><TD class=xl47 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #333399" width=65>Sum of Paid Total</TD><TD class=xl47 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #333399" width=80>Sum of Reserves Total</TD><TD class=xl45 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 43pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #333399" width=57>Sum of Recovery Total</TD><TD class=xl47 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #333399" width=65>Sum of Incurred Total</TD><TD class=xl45 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 49pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #333399" width=65>Sum of Ultimate Incurred</TD><TD class=xl47 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #333399" width=65>Sum of T&D Incurred</TD><TD class=xl45 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 49pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #333399" width=65>Sum of Capped T&D Incurred</TD><TD class=xl52 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 49pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #333399" width=65>Sum of Field2</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl48 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="14">14 </TD><TD class=xl46 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="38100.98">38,100.98 </TD><TD class=xl46 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0">0.00 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="-3505.95">(3,505.95)</TD><TD class=xl46 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="34595.03">34,595.03 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="35506.035723563858">35,506.04 </TD><TD class=xl46 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="43098.121471804996">43,098.12 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="43098.121471804996">43,098.12 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="43098.121471804996">43,098.12 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl49 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="19">19 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="65941.74">65,941.74 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0">0.00 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="68">68.00 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="66009.74">66,009.74 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="69909.411411537687">69,909.41 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="81940.716867550349">81,940.72 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="81940.716867550349">81,940.72 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="81940.716867550349">81,940.72 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl49 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="9">9 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="77182.87">77,182.87 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="11000">11,000.00 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0">0.00 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="88182.87">88,182.87 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="100048.96480230856">100,048.96 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="112518.94305622972">112,518.94 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="108983.41186983568">108,983.41 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="108983.41186983568">108,983.41 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl49 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15 x:num="7">7 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="9627.34">9,627.34 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0">0.00 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0">0.00 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="9627.34">9,627.34 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="12650.498081403393">12,650.50 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="13835.690355355064">13,835.69 </TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="13835.690355355064">13,835.69 </TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="13835.690355355064">13,835.69 </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl50 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: #a6caf0" align=right height=15 x:num="49">49 </TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="190852.93">190,852.93 </TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="11000">11,000.00 </TD><TD class=xl42 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="-3437.95">(3,437.95)</TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="198414.98">198,414.98 </TD><TD class=xl42 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="218114.91001881351">218,114.91 </TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="251393.47175094014">251,393.47 </TD><TD class=xl53 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="247857.94056454612">247,857.94 </TD><TD class=xl54 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6caf0" align=right x:num="247857.94056454612">247,857.94 </TD></TR></TBODY></TABLE>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hello chipseloy and welcome to the forum.

The clearer your information; the clearer the ideas, tips, or even possible solutions that may be suggested.

It will be useful if you provide:
- small example of your data
- examples of your formulas
- clear explanation of what you are calculating

You could prepare a very concise example that illustrates what you are doing.
You can then indicate your expected results and what you have tried.

"it doesn't work " doesn't provide useful information.

If you want an average by claim number, you may have to add a helper column and include that in your Pivot Table.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


You may want to review the messages at the top of the list of messages.

Code: 
			Sum    		Count
	Data		
Claim_Num	      Capped T&D	 Claim_Num	Avg_Claim

22			3,705,000			3	1,235,000
35			5,145,000			3	1,715,000
Grand Total		8,850,000			6	
								1,475,000
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
315
VBAEXCELNew
V
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
826
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,912
Messages
6,122,204
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top