custom pivot taable calculation based on individual cells

rty1138

New Member
Joined
Jul 23, 2010
Messages
1
i need to do some calculations with pivot tables but i get the wrong/invalid results because i believe calculated fields work with sum totals, not individual cells. my original excel sheet looks like this:

<table x:str="" style="border-collapse: collapse; width: 96pt;" width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">flavor</td> <td style="width: 48pt;" width="64">taste</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">vanilla</td> <td x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td x:num="" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">vanilla</td> <td x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">vanilla</td> <td x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">vanilla</td> <td x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">vanilla</td> <td x:num="" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">vanilla</td> <td x:num="" align="right">3</td> </tr> </tbody></table>

my pivot table looks like this:

<table x:str="" style="border-collapse: collapse; width: 215pt;" width="286" border="0" cellpadding="0" cellspacing="0"><col style="width: 76pt;" width="101" span="2"> <col style="width: 63pt;" width="84"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 76pt;" width="101" height="17">flavor</td> <td class="xl24" style="width: 76pt;" width="101">Data</td> <td class="xl27" style="width: 63pt;" width="84">Total</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">chocolate</td> <td class="xl24">Average of taste</td> <td class="xl27" x:num="4.1428571428571432" align="right">4.142857143</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17"> </td> <td class="xl30">StdDev of taste</td> <td class="xl31" x:num="1.2149857925879122" align="right">1.214985793</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">vanilla</td> <td class="xl24">Average of taste</td> <td class="xl27" x:num="2.3333333333333335" align="right">2.333333333</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt;" height="17"> </td> <td class="xl30">StdDev of taste</td> <td class="xl31" x:num="1.211060141638997" align="right">1.211060142</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" colspan="2" style="height: 12.75pt;" height="17">Total Average of taste</td> <td class="xl27" x:num="3.3076923076923075" align="right">3.307692308</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" colspan="2" style="height: 12.75pt;" height="17">Total StdDev of taste</td> <td class="xl29" x:num="1.4935759876113539" align="right">1.493575988</td> </tr> </tbody></table>


i need to have a third datafield that calculated root mean squared the following way:
sqrt(4.14^2+1.21^2) for chocolate
&
sqrt(2.33^2+1.21^2) for vanilla


i don't want this to be outside the pivot table. it want it to be integrated in it so i can drag and drop it and it will automatically generate. if anyone could help, i would greatly appreciate it. thank you!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,792
Messages
5,513,471
Members
408,953
Latest member
Skiig

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top