I have the following lease data for a building we are selling. In the past we've created a table with weighted averages for market, actual, and effective rents (they are all column headers in the attached table), using count if and sum if forumales. Pivot tables seems to make the process a lot easier, plus it allows us to use the lease dates as the row tables so compare averages over time.
My concern is the averages used in pivot tables are straight averages.
I also wanted to have a running total column. I can easily do all this manually but was having a tough time creating in pivot table.
This is with excel 2013
Hi Paul,
See workbook attached - Sheet2. It contains a PivotTable with the straight averages and running totals. Note: the running totals are based on the sum of the Actual Rent etc, not the averages.
As for calculating weighted averages, you could possibly use a Calculated Field for this (depending on the formula required):
https://www.myonlinetraininghub.com/excel-pivottable-calculated-fields
Mynda
Thanks. How did you get the running total to continue through the years. I think i figured out how to add running total but it resets every year.
Right-click a cell containing the values you want to display as a running total > Show Values As > Running Total In... > Base Field: Lease Date.
i tried that but the data resets when the year turns
Ah, I'm afraid you've reached the limitation of regular PivotTables. You'll need to use Power Pivot and write a DAX measure for that.
Mynda
Hi Paul
Maybe you could add a helper column to get the Quarters and then Pivot the result.
See if this helps.
Sunny
