mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,245
- Office Version
- 365
- Platform
- Windows
PivotTable and Access Formulas
Dear Smartest Excelers In The World,
I have this data set in Excel:
Date Units Price Sales COGS GrossProfit
1/21/2007 3 25 $75.00 $37.50 $37.50
1/15/2007 5 25 $125.00 $62.50 $62.50
I created a Pivot Table with the Date field in the Row field (label) and the Sales field in the Values (Data) area. Then I grouped the Date field by month. I assume the Pivot Table did this for me:
= 75 + 125 = $200
Then I made a Pivot Table formula named “AddSales”. The formula is this:
=COGS+GrossProfit
I assumed that this formula did this:
= (37.5 + 62.50) + (37.5 + 62.5) = 100 + 100 = $200
Then I made a Pivot Table formula named “TimesSales”. The formula is this:
=Price*Units
I assumed that this formula did this:
= (3 + 5) * (25 + 25) = 8 * 50 = $400
However, when I create the same table in Access:
Date Units Price Sales COGS GrossProfit
1/21/2007 3 25 $75.00 $37.50 $37.50
1/15/2007 5 25 $125.00 $62.50 $62.50
And create this formula in a Query:
=Price*Units
I get the answer $200
I have four questions:
1) Are my assumptions about how Excel did the Pivot Table calculations correct?
2) I assumed that the Access formula (Units*Price) would calculate the same way in n Excel Pivot Table. Why would a Pivot Table formula not calculate the same way as an Access Query formula?
3) Does Excel always aggregate the fields before calculating the operator?
4) Is there an Excel Pivot Table formula that would yield the answer $200, instead of $400?
Dear Smartest Excelers In The World,
I have this data set in Excel:
Date Units Price Sales COGS GrossProfit
1/21/2007 3 25 $75.00 $37.50 $37.50
1/15/2007 5 25 $125.00 $62.50 $62.50
I created a Pivot Table with the Date field in the Row field (label) and the Sales field in the Values (Data) area. Then I grouped the Date field by month. I assume the Pivot Table did this for me:
= 75 + 125 = $200
Then I made a Pivot Table formula named “AddSales”. The formula is this:
=COGS+GrossProfit
I assumed that this formula did this:
= (37.5 + 62.50) + (37.5 + 62.5) = 100 + 100 = $200
Then I made a Pivot Table formula named “TimesSales”. The formula is this:
=Price*Units
I assumed that this formula did this:
= (3 + 5) * (25 + 25) = 8 * 50 = $400
However, when I create the same table in Access:
Date Units Price Sales COGS GrossProfit
1/21/2007 3 25 $75.00 $37.50 $37.50
1/15/2007 5 25 $125.00 $62.50 $62.50
And create this formula in a Query:
=Price*Units
I get the answer $200
I have four questions:
1) Are my assumptions about how Excel did the Pivot Table calculations correct?
2) I assumed that the Access formula (Units*Price) would calculate the same way in n Excel Pivot Table. Why would a Pivot Table formula not calculate the same way as an Access Query formula?
3) Does Excel always aggregate the fields before calculating the operator?
4) Is there an Excel Pivot Table formula that would yield the answer $200, instead of $400?
