pivot table

Hi – appreciate some help as continue to learn about pivot tables and their uses.  Attached is a sample workbook to illustrate current problem.

How can I calculate the exceedance value for a column of data (% Grand Total from pivot table6) so for a given threshold limit value of say 2m the exceedance above this value is automatically computed (and associated plot updated) when I refresh the pivot table i.e red highlighted cells dynamically update with pivot table.

Is there any way I can amend the sheet/workbook so able to input a specific limit value eg 2.5m and determine the % value of exceedance above this value for all recorded data points? My attempt to do this is just by manually selecting computed results that are over the specified threshold limit but this is not a true accurate account of the results and cumbersome – see also on tables sheet countifs calc which I believe more accurately calculates exceedance above a specified value.  Can this calculation and or above issue be incorporated into the source table so able to include in pivot table somehow?

Lastly and minor point but why is there a slight variance in the computed grand total % values in pivottable6 and 7 eg 22.90% vs 22.66%. It appears this is maybe due to fact one table shows values as % of column total and other % of grand total but unclear how arrived at.
ee-sample.xlsx
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
Mechanical EngineerCommented:
I put the threshhold level for wave height in Pivot Tables worksheet cell T23

I changed your height formula in cell Pivot Tables worksheet cell U51 (copied down) to:
=IF(AND(U50<\$T\$23,(INT(U50)+1)>=\$T\$23),\$T\$23,INT(U50)+1)

I changed the row label formula in Pivot Tables worksheet cell V51 (copied down) to:
=U51 & " - " & U52

The Height Group column in BUOY Data worksheet then broke the 2 - 3 m height group into 2 - 2.5 and 2.5 - 3 m groups. Having done that, your PivotTables should show a break with values above and below the threshhold.

I put the following formula in cell S32 (copied down) to get the cumulative % of waves above the bottom of each height group bin:
=S31-GETPIVOTDATA(" WaveHeight",\$A\$29,"Height Group",A31)

The upper value of the wave height bin in cell T31 (copied down) is:
=ABS(MID(A31,2,5))

The cumulative percent of waves in the bin that are larger than the threshhold in cell U31 (copied down) is given by:
=IF(T31<=\$T\$23,"",S31)

I got an error when refreshing the PivotTables. I eliminated this error by adding the two new bins to the filter choices (2 -2.5 and 2.5 - 3), then sorting the row items in A to Z order and refreshing the PivotTable.
ee-sampleQ28693572.xlsx
Author Commented:
byundt - many thanks for reply.

In the tables sheet I note that cells T34 and U34 return #value errors causing the chart to plot incorrectly.  also T39, T40 and U40 cells are blank ?

When I change the input cell T23 to 2m and refresh the table updates but does not resize to correct row number and so unfortunately chart plots incorrectly. The TL values revert back to 2-3 rather than 2-2.5 etc bins and although filters selected not sure why table does not list 2-2.5 and 2.5-3 row data?  The TL column T31-T38 appears to jump from 1,2,4,5,6,7,8,9 missing 3 data out?
Mechanical EngineerCommented:
Good catch on the error in cell T34. That was my goof.

I changed the formula in cell T32 (copied down) to:
=IFERROR(--MID(A32,FIND("-",A32)+1,5),"")

During debugging, I got tired of refreshing each PivotTable and checking the boxes for all the Height Group bins being used. To overcome this vexation, I wrote a macro RefreshPT to perform these housekeeping tasks automatically. This macro uses a named range HeightBins that refers to your worksheet Pivot Tables cells V50:V61. I run the macro each time I change the value of TL Exceedence in cell T23. Note the change in file extension. You will need to enable macros when you open the workbook if you want to use this macro.
``````Sub RefreshPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant
Dim i As Long
Application.ScreenUpdating = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("HeightBins").Value
For i = 1 To 2      'Need to refresh PivotTable twice to get the row items to update properly
For Each pt In .PivotTables
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Height Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name, vHeightBins, 0)
pi.Visible = Not IsError(v)
End Select
Next

pt.RefreshTable
Next
Next
Set pt = .PivotTables("PivotTable6")
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).EntireColumn.AutoFit
End With
On Error GoTo 0
End Sub
``````
ee-sampleQ28693572.xlsm
Author Commented:
excellent and thanks for refresh macro  - had a quick play with sheet.

Am i right in saying this macro must be selected manually after each time change height TL cell T23 eg if change to 2.2 m or 3.1m etc - really handy if auto did this function.

For 2m TL cells s40 and u40 seem to be populated and not cut off at pivot table row bin lables? - is it possible to also auto sort the table so the bin labels are in order or need be manual. Oddly the shaded TL plot, plots from bin 1-2 rather than 2-3  so skewed??.

Lastly on full workbook i have a similar pivot table with exceedance for WindSpeed - how easy might it be to modify the macro to replicate wave height functions were WindSpeed is PivotTable8 and WaveHeight is pivottable14.
Mechanical EngineerCommented:
I added a Worksheet_Change sub to the codepane for the Pivot Tables worksheet. This macro will run every time a value changes on the Pivot Tables worksheet, and will call the previous macro automatically as requested. As written, the Worksheet_Change sub is watching cells T23 and T63 for changes, and will call subs that refresh the Wave Height or Wind Speed PivotTables accordingly.
``````ivate Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Range("T23")     'Watch this cell for changes. Run RefreshWaveHeightPT if a change occurs
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
Call RefreshWaveHeightPT
End If

Set targ = Range("T63")     'Watch this cell for changes. Run RefreshWindSpeedPT if a change occurs
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
Call RefreshWindSpeedPT
End If
End Sub
``````
I also added a sort feature to RefreshPT, which I renamed RefreshPT567 in anticipation that you might need a similar but different sub for WindSpeed. As written, this macro works only on PivotTables 5, 6 & 7.
``````'This code goes in a regular module sheet!
Sub RefreshWaveHeightPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant, vPT As Variant
Dim i As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("HeightBins").Value
For i = 1 To 2      'Need to refresh PivotTable twice to get the row items to update properly
For Each vPT In Array("PivotTable5", "PivotTable6", "PivotTable7")    'List your PivotTables here
Set pt = .PivotTables(vPT)
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Height Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name, vHeightBins, 0)
pi.Visible = Not IsError(v)
End Select
Next

pt.RefreshTable
pt.PivotFields("Height Group").AutoSort xlAscending, "Height Group"
Next
Next
Set pt = .PivotTables("PivotTable6")
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).EntireColumn.AutoFit
End With
Application.EnableEvents = True
On Error GoTo 0
End Sub

Sub RefreshWindSpeedPT()
'Code will be similar, but different, from that for RefreshWaveHeightPT
End Sub
``````
I assumed you would probably need different actions for WindSpeed PivotTables than for WaveHeight, and planned for two subs, each doing the needful. The one for WindSpeed PivotTables is a placeholder, however. I hope that you can copy the code for WaveHeight and make the appropriate modifications. I've commented the bits you may need to change to make that job easy.

Please try to make the required tweaks to your real workbook. If you have trouble, post back in this thread along with a copy of the real workbook and the code modifications you made to suit the actual layout.
ee-sampleQ28693572.xlsm
Author Commented:
Byundt,

I have tweaked my full workbook for windspeed and wave height using your code so when trigger cells changed the PTs auto update. This works to a fashion - my problem is that the PTs do not auto sort ascending  and the attached slicers default to no filters selected so I need to manually select all relevant data filters and sort PTs ascending.  How can the code be amended to correct this.?

Sub RefreshWaveHeightPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant, vPT As Variant
Dim i As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("HeightBins").Value
For i = 1 To 2      'Need to refresh PivotTable twice to get the row items to update properly
For Each vPT In Array("PivotTable13", "PivotTable14", "PivotTable15", "PivotTable20")    'List your PivotTables here
Set pt = .PivotTables(vPT)
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Height Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name, vHeightBins, 0)
pi.Visible = Not IsError(v)
End Select
Next

pt.RefreshTable
pt.PivotFields("Height Group").AutoSort xlAscending, "Height Group"
Next
Next
Set pt = .PivotTables("PivotTable14")
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).EntireColumn.AutoFit
End With
Application.EnableEvents = True
On Error GoTo 0
End Sub

and

Sub RefreshWindSpeedPT()
Dim pt As PivotTable
Dim pi As PivotItem
Dim rg As Range
Dim v As Variant, vHeightBins As Variant, vPT As Variant
Dim i As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
With Worksheets("Pivot Tables")
vHeightBins = Range("SpeedBins").Value
For i = 1 To 2      'Need to refresh PivotTable twice to get the row items to update properly
For Each vPT In Array("PivotTable7", "PivotTable8", "PivotTable2", "PivotTable21", "PivotTable12", "PivotTable17", "PivotTable24")    'List your PivotTables here
Set pt = .PivotTables(vPT)
pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
For Each pi In pt.PivotFields("Speed Group").PivotItems
Select Case pi.Name
Case "#N/A", "(blank)"
pi.Visible = False
Case Else
v = Application.Match(pi.Name, vHeightBins, 0)
pi.Visible = Not IsError(v)
End Select
Next

pt.RefreshTable
pt.PivotFields("Speed Group").AutoSort xlAscending, "Speed Group"
Next
Next
Set pt = .PivotTables("PivotTable8")
Set rg = pt.TableRange1
rg.Rows(rg.Rows.Count).EntireColumn.AutoFit
End With
Application.EnableEvents = True
On Error GoTo 0
End Sub

Cell x35 on windspeed PT appears blank and should probably indicate >=50 –does formula need to be modified for this? Similar issue on cell x235 for wave height although note data extends below row labels for some reason?

Wondering if there is a better way to plot the exceedance (red shaded area) plots as currently skewed and not vertical from defined TL limit eg 2m height TL exceedance plots from <1.0m bin rather than from 2-3 bin, similarly speed plots from 15-20 bin rather than 25-30 bin given a defined 25knts TL .

I have PMd you full workbook so able to see above and tweaks made.
Author Commented:
Discovered HeightBins and SpeedBins ranges were not pointing to correct cells in my workbook! - corrected to point to

=Index!\$I\$5:\$I\$16   and   =Index!\$C\$18:\$C\$28 respectively on full workbook but still issues with sorting groups?

Is it just PT 14 that the sort will work on or all height PTs eg 13,14,15,20.  similarly for speed PTs 7,8,2,12,17?
Mechanical EngineerCommented:
The macros are expecting named ranges SpeedBins and HeightBins, against which they check whether to display bins in the PivotTables. These named ranges don't exist in your workbook, and must be added. They should point to Index!\$I\$5:\$I\$16 and Index!\$C\$18:\$C\$28.

I tried testing the code, but your production file locks up my Excel. I use Excel 2013 on Windows 7 on my 2008 Mac laptop, running everything on a virtual machine using Parallels. It is possible that my virtual machine doesn't have enough memory to run your production file, though it was able to run the code in the smaller workbook posted in the question.

The sort is supposed to work on all the PivotTables you have listed in the Array statement in the macro.
Author Commented:
Hi byundt, I have included name ranges and pointed to index etc but when change TL and code does refresh PTs do not update/sort - must be missing something.

I am running full workbook on win7 pc with excel 2010 and 4GB ram. I have PMd you a reduced workbook so hopefully able to check and troubleshoot my error?  can you think of a way to get the shaded plot to go vertical and not sloped when charting exceedance??
Mechanical EngineerCommented:
When you post a workbook, whether it be in the question thread or in a PM, Experts Exchange limits you to 40 characters for the filename. Making matters worse, they silently truncate the rightmost part of the filename if it exceeds that limit. This means that you lose part or all of the file extension, and never receive any warning that this was done.

I mention the above because your file names in the PM already exceed the limit. I guessed that you were posting a .xlsm file and was able to open it, but experts who hadn't stumbled across this "feature" before will be stymied.
Author Commented:
OK, good to know - thanks for the heads up. Hopefully received ok and yes is a xlsm file.
Mechanical EngineerCommented:
One approach for getting a sharp rise on the area chart is to return an error value instead of an empty string in the values being plotted. For example, change the formula (copied down) in Pivot Tables worksheet cell Y25 to:
=IF(X25<=\$Y\$19,NA(),W25)

The failure to sort is because the bin labels are treated as text, so "5 - 10" sorts after "40 - 45" and ">45" sorts before "0 - 10". One way to overcome this problem is to format the bin label values with two digits and eliminate ">". For example,  "<5", "05 - 10", "10 - 20", "20 - 30", "30 plus". I made these changes to the two named ranges on the Index worksheet.
Author Commented:
The good news is that the new code in Y25 etc works great and sorts the shaded plot. Also saved file as xlsb and halved size - excellent tip.

The bad news is that after a number of attempts to test the macro for sorting lables excel goes through the motions (code appears to work but tables do not update, spinning circle indicating work in progress finishes/disappears) then excel freezes where unable to move cursor etc - no error messages appear  - task manager shows as 'running' and processor is around 00-01 so not doing anything in background.  Forced to quit excel each time to resolve. It seems therefore somehow refresh code for sorting crashes excel even on reduced data size .

Just noted that the source of data TABLE1 is pointing to 'ee-PT100715.xlsm'!Table1 and not 'BUOY Data'!\$A\$1:\$R\$27182 in workbook and reduced data set row size. I changed the source of data link for PTs (7,8,2) (after removing clicer connections for all PTs) and re ran - Pleased to say code appears to fully work so looks like issue was the source data link was not updated after reduced file data size etc.

Why are x25 and y25 showing #value errors and x35 is blank?

Is there anyway to update the source data table for all PTs at once or just have to select each in turn and update?
Mechanical EngineerCommented:
You might consider putting a 0 in cell X25. That will eliminate the error caused by the parsing formula. Cell Y25 should then return #N/A error value (needed so chart shows squared up left edge for exceedance.

X35 will be blank if the parsing formula doesn't find a hyphen in cell E35. I suggest just typing in what you want to display in cell X35 instead of using the formula.

I had previously faced the problem of the source data being on a different workbook, and added the following statement to the macros to address it:
``````pt.SourceData = "'" & ActiveWorkbook.Name & "'!Table1"
``````
I hope that you won't have to fix the problem manually in the future.
Author Commented:
Thanks - where does the SourceData code go  - in PT sheet or module1 / module 2 or both ? of workbook and where within the code should it be placed?

Any thoughts on why grand total values in for example PT8 do not update/refresh when change Y19?
Mechanical EngineerCommented:
If you look, the SourceData statement has always been there. It is already in the macros on both Module1 and Module2.
Author Commented:
Sorry, missed that.

But why then do i need reset sourcedata for PTs if this as i understand it auto updates PTs for Table1 range and why are the PTs data values not apparently updating even though the bin lables now sort correctly eg if change TL from 25 to 30 data values remain same
Author Commented:
similar refresh issue with the height TL and its data values if change relevant TL.

When testing for Height threshold limit of 2.5m TL I noted that the index bin assigned for this value is 03-03 which is a bit confusing/misleading - not sure if possible to tweak text formula to correctly show bin 2.5-3 as before if possible ??
Mechanical EngineerCommented:
To get the height bins to sort correctly, I made the following tweaks to formulas in Index worksheet cells:
=" <" & H5        formula for I4
=IF(H5<10,CHAR(160),"") & H5 & " - " & H6             formula for I5 (copied down)
="Over " & H15         formula for I15

To be consistent, I changed the formulas for speed bins cells C18:C28 to:
=" <" & B19                 formula for cell C18
=IF(B19<10,CHAR(160),"") & B19 & " - " & B20            formula for cell C19, copied down
="Over " & B28           formula for cell C28

With the above changes, the Data...Sort menu item sorts the values correctly--both with and without a height threshold with a decimal fraction (e.g. 2 or 2.5).

With respect to issue on PivotTables not changing, are you saying that the values change if you manually refresh the PivotTable after the macro has run?

FWIW, I made the changes on my copy of your workbook, and the macro ran with a change to W218, but locked up my Excel with a change to Y19.
Author Commented:
I have applied above changes to my reduced workbook and tested. To help troubleshoot I have further reduced data to only 1000 rows and re created Table1 range so hoepfully you will be able to test run your end.  I disconnected all slicer connections. For all the PTs I manually (one by one) pointed all to the new Table1 range (some were defaulting to original full workbook range of 84734 and so not updating data values even though data values did not exist below 1000 rows) and updated all PT SPEED/HEIGHT/PERIOD filters to include new height and speed bins (most fliters were initially unchecked). When first updated (changed y19 to 20) the row lables did not sort correctly so manually corrected and saved file.  I then re changed y19 to 25  - macro  ran and again the labels did not sort correctly.  see file.

Testing the height TL I ran at 2.5m from 2m and PT DID sort correctly  - re ran at 2m and table again auto sorted correctly including exceedance plot and values etc.

So seems height sort works but speed sort does not??
Mechanical EngineerCommented:
I believe the main problem was the fact that PivotTable24 is missing. I removed it from the Array statement in sub RefreshWindSpeedPT, and now the code runs to completion when I change the value in cell Y19.

I should also point out that PivotTable21 does not have any Speed Bins being displayed, but this does not appear to be causing a problem.

Experts Exchange Solution brought to you by