All - Im not sure if this is a complicated ask or if I'm not figuring out a way to articulate it easily...

I am trying to make a rental analysis for an apartment building. For each unit in the apartment, there are 3 different rates that can be charged for occupancy, or there can be no occupancy and therefore no rate.

Im looking for an analysis that averages the overall rental rate charged through a period of time, to the extent a building was occupied. Further, I would like to be able to group the averages based upon unit grouping (units 100-199, units 200-299, etc...)

There are ways to do this by multiple tables and then grouping averages within the table, but I am looking for a formula that allows me to bypass having to make a big table.

Just rereading this, i can already tell it is hard to follow, so I've attached an example spreadsheet with way more explanation. (PS couldnt figure out how to attach the spreadsheet to this post, so here is a dropbox link)

https://www.dropbox.com/s/99afhd1flk...book.xlsx?dl=0

Please somebody help!! Thank you in advance!