pivot table formula
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: pivot table formula

  1. #1
    Guest

    Default

     
    I'm trying to use a custom pivot table formula with an "if" command. Can I set up a pivot table field that basically says, if field1="yes", then field2*field3, else 0 and thereby get the sumproduct of fields 2 and 3 wherever field 1 is equal to "yes". This would seem to be more logical than creating intermediate fields in my database.
    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-11 16:39, Anonymous wrote:
    I'm trying to use a custom pivot table formula with an "if" command. Can I set up a pivot table field that basically says, if field1="yes", then field2*field3, else 0 and thereby get the sumproduct of fields 2 and 3 wherever field 1 is equal to "yes". This would seem to be more logical than creating intermediate fields in my database.
    Thanks
    It may seem more logical, but it wouldn't (if it could be done -- and it can't) achieve your desired end. It would fowl up the order of operations since Calculated Fields sum the fields before performing the arithmetic operations. See the Help topic for "Syntax for calculated field and item formulas in PivotTables" where it states "Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula".

    You'd get the product of the sums instead of the sum of the products!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com