I have a grid of Bedroom Unit Mixes along with the weighted average and sum monthly for 0-6 bed units
i'm looking for a formula to calculate the Median Unit as well as Median Unit Average Rent
Example Solutions
00044226RAP only has 3 Bedrooms so the median Unit is "3" and the 'Median Unit Average rent' is "479.00"
AK020002001 has 51 units, with the median unit being "2" and thus the Median Unit Average rent' is $1,362.00"
AK020005001 has 41 1-bed and 41 2-Bed. The calculation would need to split this and be '1.5' and then split the Average Rent = $1252+$1416 = 2668 / $1,334
Any solutions or options are greatly appreciated
Unit Mix Monthly Rents Monthly Average Rent
i'm looking for a formula to calculate the Median Unit as well as Median Unit Average Rent
Example Solutions
00044226RAP only has 3 Bedrooms so the median Unit is "3" and the 'Median Unit Average rent' is "479.00"
AK020002001 has 51 units, with the median unit being "2" and thus the Median Unit Average rent' is $1,362.00"
AK020005001 has 41 1-bed and 41 2-Bed. The calculation would need to split this and be '1.5' and then split the Average Rent = $1252+$1416 = 2668 / $1,334
Any solutions or options are greatly appreciated
Unit Mix Monthly Rents Monthly Average Rent
| Row Labels | 0 | 1 | 2 | 3 | 4 | 5 | 6 | Units Total | 0 | 1 | 2 | 3 | 4 | 5 | 6 | Rents | 0 | 1 | 2 | 3 | 4 | 5 | 6 | Annual Rent |
| - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
| 00044226RAP | 0 | 0 | 0 | 25 | 0 | 0 | 0 | 25 | $0.00 | $0.00 | $0.00 | $11,975.00 | $0.00 | $0.00 | $0.00 | $12,025.00 | - | - | - | $479.00 | - | - | - | $144,300.00 |
| AK020002001 | 0 | 10 | 30 | 11 | 0 | 0 | 0 | 51 | $0.00 | $10,790.00 | $40,860.00 | $18,480.00 | $0.00 | $0.00 | $0.00 | $70,232.00 | - | $1,079.00 | $1,362.00 | $1,680.00 | - | - | - | $842,784.00 |
| AK020004001 | 0 | 30 | 60 | 18 | 0 | 0 | 0 | 108 | $0.00 | $34,350.00 | $84,180.00 | $29,448.00 | $0.00 | $0.00 | $0.00 | $148,194.00 | - | $1,145.00 | $1,403.00 | $1,636.00 | - | - | - | $1,778,328.00 |
| AK020005001 | 0 | 41 | 41 | 0 | 0 | 0 | 0 | 82 | $0.00 | $51,332.00 | $58,056.00 | $0.00 | $0.00 | $0.00 | $0.00 | $109,552.00 | - | $1,252.00 | $1,416.00 | - | - | - | - | $1,314,624.00 |
| AK020006001 | 0 | 23 | 0 | 0 | 0 | 0 | 0 | 23 | $0.00 | $37,628.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $37,674.00 | - | $1,636.00 | - | - | - | - | - | $452,088.00 |
| AK020007002 | 0 | 12 | 28 | 12 | 0 | 0 | 0 | 52 | $0.00 | $12,048.00 | $33,040.00 | $16,656.00 | $0.00 | $0.00 | $0.00 | $61,848.00 | - | $1,004.00 | $1,180.00 | $1,388.00 | - | - | - | $742,176.00 |
| AK020102002 | 0 | 120 | 0 | 0 | 0 | 0 | 0 | 120 | $0.00 | $129,240.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $129,480.00 | - | $1,077.00 | - | - | - | - | - | $1,553,760.00 |
| AK020102005 | 0 | 96 | 0 | 0 | 0 | 0 | 0 | 96 | $0.00 | $121,152.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $121,344.00 | - | $1,262.00 | - | - | - | - | - | $1,456,128.00 |
| AK020102008 | 0 | 0 | 22 | 0 | 0 | 0 | 0 | 22 | $0.00 | $0.00 | $29,150.00 | $0.00 | $0.00 | $0.00 | $0.00 | $29,194.00 | - | - | $1,325.00 | - | - | - | - | $350,328.00 |
| AK020102010 | 0 | 30 | 0 | 0 | 0 | 0 | 0 | 30 | $0.00 | $42,810.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $42,870.00 | - | $1,427.00 | - | - | - | - | - | $514,440.00 |
| AK020102011 | 0 | 0 | 10 | 7 | 0 | 0 | 0 | 17 | $0.00 | $0.00 | $12,050.00 | $8,799.00 | $0.00 | $0.00 | $0.00 | $20,883.00 | - | - | $1,205.00 | $1,257.00 | - | - | - | $250,596.00 |
| AK02M000011 | 0 | 4 | 20 | 6 | 0 | 0 | 0 | 30 | $0.00 | $5,200.00 | $32,300.00 | $11,130.00 | $0.00 | $0.00 | $0.00 | $48,690.00 | - | $1,300.00 | $1,615.00 | $1,855.00 | - | - | - | $584,280.00 |
| AK02M000012 | 0 | 7 | 13 | 47 | 47 | 0 | 0 | 114 | $0.00 | $6,902.00 | $15,197.00 | $69,560.00 | $75,670.00 | $0.00 | $0.00 | $167,557.00 | - | $986.00 | $1,169.00 | $1,480.00 | $1,610.00 | - | - | $2,010,684.00 |
| AK02M000023 | 0 | 13 | 26 | 11 | 6 | 0 | 0 | 56 | $0.00 | $13,221.00 | $31,148.00 | $16,599.00 | $9,834.00 | $0.00 | $0.00 | $70,914.00 | - | $1,017.00 | $1,198.00 | $1,509.00 | $1,639.00 | - | - | $850,968.00 |
| AK02R000003 | 0 | 16 | 8 | 0 | 0 | 0 | 0 | 24 | $0.00 | $22,736.00 | $12,664.00 | $0.00 | $0.00 | $0.00 | $0.00 | $35,448.00 | - | $1,421.00 | $1,583.00 | - | - | - | - | $425,376.00 |
| AK02R000004 | 0 | 23 | 1 | 0 | 0 | 0 | 0 | 24 | $0.00 | $34,040.00 | $1,699.00 | $0.00 | $0.00 | $0.00 | $0.00 | $35,787.00 | - | $1,480.00 | $1,699.00 | - | - | - | - | $429,444.00 |
