sum based on multiple conditions

[katod1]

Hi

I've attached a sample spreadsheet. What I want to do is calculate rental income. Rental income is calculated as % x value of the property + sum of construction costs for the number of periods passed ie if property bought in 1st period then in period 4 rental income = (value + 4x construction costs for a periods) then in periods 5-8 rental income is y%. However in each period new properties can be bought so the total rental income is the sum of both.

 

[Faseeh]

Hi Katod1,

Can you show me the step wise calculation for how u have calculated the last rental income row, am unable to calculate those numeric values. Thanks.

 

[katod1]

Please see attached

I would happily do it this way but I need something dynamic. Any solution would be helpful! All of my methods are becoming so terribly complicated that others will get lost in understanding the calculation

 

[Faseeh]

Hi,

Sorry for this much late reply.. Please see attached file with yellow cells.

 

[katod1]

I actually need a calculation for rental income (row 32) without separating out each property instead using the information above row 32. if i have a dynamic calculation in row 32 using the above information I can then do scenario testing based on the number of properties bought, the value of those properties and the amount of rental income generated. Am I able to do this or do I need to set up some kind of other worksheet that I can feed the data through that will allow scenario testing?

 

[Faseeh]

Hi Katod1,

A single line solution to your problem is difficult, i suggest you to pick up, say 10 projects or any max number and build a model over that. Please note that i have understood what is in row 32 but am unable to understand the question in its entirety. what is the purpose of data in cells A7:F16 i can correctly correlate.

 

[NARAYANK991]

Hi ,

I suggest you first rearrange your one tab into multiple tabs , so that there is a clear separation of data and calculations ; you can aid in this by color coding your cells ; see the upload for one method.

If I go through your worksheet(s) , there are the following items , in cells F2 and F3 , which seem to be repeated in cells F9 and F10 ; is this so ? Can you please confirm ?

Value of Multi-family
Construction Costs Multi-family

Secondly , there is an item named : Monthly Construction Cost , which I think should be renamed to :

Quarterly Construction Cost

since you are dividing the total cost by the number of quarters to arrive at this value.

This same value appears in your calculations , in cell F6 , where it is shown as coming from the location :

[Projections.xlsx]Conservative Case'!F46

I think you should first go through your files , and properly arrange data so that :

1. There is absolutely no repetition

2. Any calculations are done only in one place , and thereafter referred to everywhere , instead of repeating the calculations in several places.

3. Preferably all data should be in one place , be it a workbook or a worksheet , and all calculations should be in a different place.

4. Rather than have data spread over multiple workbooks , have everything in just one workbook , since it is easier to work with one file. You can easily protect worksheet tabs which you do not want others to access / modify.

If a project is structured well , generating reports becomes that much easier ; if your data is not well-organized , all formulae will become that much more complicated.

Narayan