Posts by Rickbale

Using the CalculatedFields.Add Method to create a calculated field. Refer Image 1a which shows a PivotTable report named "PivotTable1" showing Sales using the summary function of Sum. Image 1b shows the Pivot report with a new calculated field added "Variance-%", using the below code. Note that the source range field names are "Sales" and "Budgeted Sales" on which calculations are based.

Sub PivotTableCalculatedFields1()




Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")


'for empty cells in the data area of a PivotTable report, show a specified value, using the PivotTable.NullString Property:
PvtTbl.NullString = "0"
PvtTbl.DisplayNullString = True


PvtTbl.CalculatedFields.Add Name:="Variance", Formula:="=IF(OR(Sales=0,Budgeted Sales=0),0,(Sales - Budgeted Sales)/Budgeted Sales)"


With PvtTbl.PivotFields("Variance")


.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "0.00%"
.Caption = "Variance-%"


End With




End Sub




Admin

Follow these steps

• Right-click a cell inside the pivot table.
• Select “Pivot Table Options…” from the menu.
• On the Layout & Format tab, uncheck the “Autofit on column widths on update” checkbox.
• Press OK.

The columns will NOT automatically resize when changes are made to the pivot table.

To calculate Polygon Area the formula can be represented by the expression:

If the points (x, y) of the polygon are known, we can apply the previous equation using simple arithmetical calculations. In this example (taken from Wikipedia) we have 5 points, so, first we calculate the xi*yi+1 (i.e. first point: 3 * 11 = 33 etc.) and then the xi+1*yi (i.e. first point: 5 * 4 = 20 etc.) for each point. Note that for convenience purposes we repeated the coordinates of the first point at the end of the table.

Next, we sum the xi*yi+1 (i.e. 207) and the xi+1*yi (i.e. 267). Finally, we take the absolute value of the difference divided by 2 (i.e. ABS(207 – 267)/2 = 60/2) and the result is the desired polygon area (i.e. 30).

Regards,

Rick Bale

Normally, if you want to deselect a cell or multiple cells from a selection, you just need to hold down the CTRL key and click on the cells you want to deselect. If you want to unselect a range of selected cells, you need to hold down the CTRL key and drag the range you want to deselect.

Thanks

To Change or remove workbook passwords

• Open the workbook that you want to change or remove the password for.
• On the Review tab, click Protect Sheet or Protect Workbook.
• Click Unprotect Sheet or Protect Workbook and enter the password.
• Clicking Unprotect Sheet automatically removes the password from the sheet.
• To add a new password, click Protect Sheet or Protect Workbook, type and confirm the new password, and then click OK.
• To remove a password from the workbook, click Protect Workbook, and then click OK without entering a new password.

Hope this information was helpful