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!
<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!
