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.

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


    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.


    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