Forum Discussion

developer8888's avatar
developer8888
Copper Contributor
Oct 08, 2023

Automatically excluding cells with no data

All,   I'm trying to create a robust real estate development model. It's laid out as follows: Input sheets (rent roll, other income, vacancy, operating expenses) Calculation sheet (simply aggreg...
excel
Macros and VBA
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 10, 2023

developer8888 

I think this is spiralling out of control.  To produce one table for your output sheet requires

= LET(
    table,    FILTER(rentRoll, CHOOSECOLS(rentRoll, 1)<>"-"),
    numUnits, CHOOSECOLS(table, 3),
    unitMix,  CHOOSECOLS(table, 4),
    acSF,     CHOOSECOLS(table, 7),
    grSF,     CHOOSECOLS(table, 9),
    rent,     CHOOSECOLS(table, 10),
    mInc,     CHOOSECOLS(table, 12),
    sumUnits, SUM(numUnits),
    sumUnMix, SUM(unitMix),
    wtAVacSF, SUM(numUnits * acSF) / sumUnits,
    wtAVgrSF, SUM(numUnits * grSF) / sumUnits,
    wtAVrent, SUM(numUnits * rent) / sumUnits,
    incPerSF, wtAVrent / wtAVacSF,
    sumMoInc, SUM(mInc),
    sumAnInc, 12*sumMoInc,
    VSTACK(rentRollHdr, table,
      HSTACK("Totals / Averages", "", sumUnits, sumUnMix, "", "", 
        wtAVacSF, "", "", wtAVrent, incPerSF, sumMoInc, sumAnInc)
    )
  )

to allow for the differences in the totals row.  

An alternative strategy would be to use Excel Tables for all input.   The tables resize to accommodate the data as it is input so the filtering is no longer needed and the column totals move with the data.

Resources