+ Reply to Thread
Results 1 to 7 of 7

Pivot Tables - Calculated field

  1. #1
    Registered User
    Join Date
    02-25-2022
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    9

    Pivot Tables - Calculated field

    Hi Guys

    Just wondering what formula I need when creating a calculated field based on finding a percentage based on count information.

    the current calculation is resulting in an Error.

    Ive attached a example document and you can see in Sheet 2 where the error is.

    Thanking you for your help in advance
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Pivot Tables - Calculated field

    Your field is Money/Colour. You can't divide by a color; it's treated as a zero so you get a divide-by-zero error.

    What is it you're trying to get?
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Pivot Tables - Calculated field

    I guess you need to use a formula something like;

    Please Login or Register  to view this content.

    and, from "Value Field settings" ... "Show values as" >> "% of Grand Total"

    i.e., you've to perform a mathematical operation on "numerical values" as stated by Jeff.
    Attached Images Attached Images
    Last edited by Haluk; 05-27-2022 at 05:02 PM.

  4. #4
    Registered User
    Join Date
    02-25-2022
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Pivot Tables - Calculated field

    Thank you all for your help and the above does seem to be getting there

    however if you look at row 8 7 of 17 does not equal 16.28%??

    it seems to be getting the percentages now but calculating something different?

  5. #5
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Pivot Tables - Calculated field

    Check the attached file to see if it suits to your needs;
    Attached Files Attached Files
    Last edited by Haluk; 05-28-2022 at 05:56 PM.

  6. #6
    Registered User
    Join Date
    02-25-2022
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Pivot Tables - Calculated field

    Certainly does - can you show your workings?

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Pivot Tables - Calculated field

    See the helper columns "CntColour" and "CntMoney" in source table on Sheet1 where I've used the formulas given below in cells E3 and F3;

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Then, I've inserted these 2 fields in the Pivot Table's "Values" area and added a "Calculated Field" where I've used the below formula;

    Please Login or Register  to view this content.
    I named this calculated field as "Percent" and by right clicking a random cell in the "Percent" field, I choosed "Number Formatting" from the pop-up menu and choosed "Percantage" for formatting the field.

    Hope this helps,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculated field (Sum) in Pivot tables
    By Skutter in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-02-2019, 01:01 PM
  2. Pivot Tables - Calculated Field Help
    By keithfender in forum Excel General
    Replies: 3
    Last Post: 06-18-2015, 12:10 PM
  3. Pivot Tables Calculated Field
    By yawnzzzz in forum Excel General
    Replies: 2
    Last Post: 05-23-2012, 09:29 AM
  4. Replies: 1
    Last Post: 04-12-2010, 01:01 PM
  5. Calculated field in Pivot Tables
    By JILL in forum Excel General
    Replies: 2
    Last Post: 04-28-2006, 11:00 AM
  6. Calculated field in pivot tables
    By nc in forum Excel General
    Replies: 1
    Last Post: 12-02-2005, 09:40 AM
  7. pivot tables:calculated field
    By mariagloria in forum Excel General
    Replies: 2
    Last Post: 02-28-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1