# Posts by Rickbale

• ## Add a calculated field to a pivot table

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.

• ## Prevent pivot table columns disappearing

• 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.

• ## Calculate Polygon Area

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

• ## Any way to deselect a select in VBA?

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