PivotTable and Access Formulas

[mgirvin]

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?

 

[Fazza]

Hi, Mike.

I'm just about to go home for the weekend so will be brief. Like this thread from yesterday, http://www.mrexcel.com/forum/showthread.php?t=384129 you can create the formula in SQL. So, like,

SELECT Date, Units, Price, Units * Price AS [TimesSales], Sales, COGS, GrossProfit
FROM YourDataDefinedName

Excel PT calculated field is -like you say - on aggregates IIRC. Gotta go now, cheers, Fazza

 

[mgirvin]

Dear Fazza,

Thank you for the information!!

From the information in the other post (A=B is 1, otherwise 0), really I could also just create an extra calculated column in the data set to calculate units * price, right?

As for my other questions (Fazza, you answered this one: Is there an Excel Pivot Table formula that would yield the answer $200, instead of $400?), anyone:

I have two remaining questions:

1) Does Excel always aggregate the fields before calculating the operator?
2) Are my assumptions (from 1st post) about how Excel did the Pivot Table calculations correct?

 

[RoryA]

1. Yes.
2. Yes. (see 1)

 

[mgirvin]

Dear Fazza and rorya,

Thank you very much!