I am struggling with figuring out how to calculate weighted averages with the data i have. i am trying to show leasing trends of a building that was recently built. The concept is average rents per unit and average rent/sf per quarter. The average rent per unit is right. But the rent/sf is wrong.
The file is attached. In short, the weighted average rent/sf is the sum of the rents divided by the sum of the square footage. I can't figure out how to do this and everything online discusses a helper column but it isn't making sense to me.
Paul
Hi Paul,
no file is attached. You have to browse for the file and then click the 'Start Upload' button.
Regards
Phil
And we only do volunteer work here! 🙂
Hi Paul
Without the file, I'm guessing at what you're trying to do. I imagine you have a table with n rows for n rental units, a column for rent (each unit), a column for sf (each unit), and a row for column totals (Σ).
Average rent per unit is Σ(rent)/n, right?
A helper column would be a new column in the table to calculate rent/sf for each unit. Then Average (rent/sf) = Σ(rent/sf)/n.
However you want weighted average rent/sf. I assume you want weighting by sf. You don't even need the helper column, since it cancels out when the rent/sf is multiplied by sf. As you said,
"the weighted average rent/sf is the sum of the rents divided by the sum of the square footage"
or Σ(rent)/Σ(sf). In what way is your calculated value wrong? 
Laura
Laura Martin said
Hi PaulWithout the file, I'm guessing at what you're trying to do. I imagine you have a table with n rows for n rental units, a column for rent (each unit), a column for sf (each unit), and a row for column totals (Σ).
Average rent per unit is Σ(rent)/n, right?
A helper column would be a new column in the table to calculate rent/sf for each unit. Then Average (rent/sf) = Σ(rent/sf)/n.
However you want weighted average rent/sf. I assume you want weighting by sf. You don't even need the helper column, since it cancels out when the rent/sf is multiplied by sf. As you said,
"the weighted average rent/sf is the sum of the rents divided by the sum of the square footage"
or Σ(rent)/Σ(sf). In what way is your calculated value wrong?
Laura
That's the jist and thank you. I messed up not attaching the file. The issue I omitted was it's within a pivot table. I used pivot table because there are twenty different floor plans that need to get grouped and then we show average rent and average rent per sf. It's clumsy trying to identify all the unique floor plans and start doing sum if everywhere when pivot table accomplishes that in seconds. I think I need to use a calculated field. But have never done that
Ah, it's a Pivot Table. I think you can "subtotal" by average - that is, the group subtotal is actually an average.
