+ Reply to Thread
Results 1 to 2 of 2

Calculate Norm Dist using pivot table

  1. #1
    Registered User
    Join Date
    Toronto, Canada
    MS-Off Ver
    Excel 2010

    Calculate Norm Dist using pivot table

    Hi all,
    I am looking to create a monthly report containing normal distribution graph using a pivot table. Its important to use pivot tables as the data keeps changing month-to-month. I was able to create Column containing Average, StDev as they are calculated fields provided by Excel. However, I need to create another column which will contain norm.dist(row_item, grandtotal mean, grandtotal stDev). You can create custom formula in pivot tables however you need to insert the original fields. But this formula requires the grandtotal values.

    Any ideas on how I can achieve that will be helpful! Thanks soo much! report is attached
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365

    Re: Calculate Norm Dist using pivot table

    I am not quite sure what you are trying to do here. Are you trying to get the standard deviation of the data? If so, try the following: overlay the values you want to analyze with a named dynamic range and do your analysis on this name.

    In the attached I have a named range: Value_List =OFFSET(Sheet3!$B$4,0,0,COUNTA(Sheet3!$A:$A)-2,1)

    This name points to the range between Row Lables and Grand Total in column B regardless of how many rows wind up in the pivot table.

    This article explains named dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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. Norm.Dist on Test Scores for a Curve
    By jsraadt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2014, 04:24 PM
  2. [SOLVED] Calling Application.NORM.S.DIST issue???
    By Boo123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2014, 12:15 PM
  3. [SOLVED] Pivot Table - Calculate a Value
    By some_evil in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 04-04-2014, 01:00 AM
  4. Calculate orders past agreed dates in pivot table and averages in pivot tables
    By applesandpears in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-18-2012, 05:26 PM
  5. Calculate Alpha, Beta for use in Beta Dist function
    By Rapjam in forum Excel General
    Replies: 2
    Last Post: 09-17-2012, 09:58 PM
  6. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  7. Need to see and calculate dates in Pivot Table
    By SJMaye in forum Excel General
    Replies: 1
    Last Post: 06-03-2011, 09:08 AM

Tags for this Thread


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