# Calculate Norm Dist using pivot table

1. ## 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

2. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

#### 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