Column Calcs

[ara.darakjian]

I have a financial model I am building in excel. There are multiple unit types and each unit type has a monthly absorption rate.
I have attached the workbook.

The columns I am referencing are on the Project Assumptions sheet, columns Q, R & W

Column Q - Monthly Absorption
Column R - # of Units by Unit Type
Column W - Monthly Unit Rent

I am trying to figure how to write an efficient formula that will calc the rent based absorption rates and sum the unit types. The monthly absorption is the number of units that lease up on a monthly basis. Rental income is calculated by multiplying the monthly rent by the monthly absorption plus the previous months rental income.

The formula I have written is "=G19+IF(H2*'Project Assumptions'!$Q4>='Project Assumptions'!$R4,0,'Project Assumptions'!$Q4*'Project Assumptions'!$W4)" and this works for 1 unit type. I would have to write this same formula 48 times to account for every unit type. The formula is on row 19 on The Operating Forecast tab.

Is there a better way?

[Joske920]

in this case you can just write the formula 1 time and pull to the and of your dashboard

using the $ in your formulas makes it easy that way or select the range in the formula and press F4

the $ means static so it will not change when you pull formulas

put this in D19

Please Login or Register  to view this content.

and pull the formula to the end of your dataset

might need to change the ; to , in the formula

[ara.darakjian]

Thank you. I figure it out using @[Column Name]

[AliGW]

Thanks for letting us know.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.