Looking for formula to run multiple conditional averages

[djh30]

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!

[protonLeah]

deleted.....

[MarvinP]

Hi djh,

I needed to delete column F(?) to make a table of your data. I've replaced your big table with a CSE Array formula of:

=IFERROR(SUM(CHOOSE((F5:Q5)+1,0,C5,D5,E5))/COUNTIF(F5:Q5,">0"),0)

I then created a Pivot Table and grouped the units by 10s to get close to what you want. See the attached. Hope this gets you closer to you answer.

CSE Choose Unit Number Price Aveage Group.xlsx

A CSE formula need to be ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.