Question description:
This user has given permission to use the problem statement for this
blog.
Hello, putting together a projection where for every $100K in revenue i want to add 1 new real estate investment. im using "IF" fucntions, but if I made 300K in one year id want to add 3 units per this projection, but my IF function only adds 1 if revenue is greater than $100K
Solved by S. H. in 43 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
05/09/2018 - 12:36
Welcome, Thanks for choosing Got It Pro-Excel.
User
05/09/2018 - 12:37
Hi! thanks for helping
Excelchat Expert
05/09/2018 - 12:37
Do you have sample data?
User
05/09/2018 - 12:38
Yes i do, i just pasted it in
User
05/09/2018 - 12:39
basically want to create a formula in D11 that calculates the units based on the Cumulative equity. I want it to populate the Unit D11 column based on whats in row 15
Excelchat Expert
05/09/2018 - 12:39
Will you mind if you upload the actual file?
User
05/09/2018 - 12:39
Yes go ahead
Excelchat Expert
05/09/2018 - 12:40
Okay.
Excelchat Expert
05/09/2018 - 12:40
How can we know the number of units by using the Cumulative Equity?
User
05/09/2018 - 12:41
For every $100K in equity, I'd like to add a unit. So if in year 2, equity is 200K, we would add one unit so there would be 2. Or if in year 2 there was $500K in equity, we would add 4
User
05/09/2018 - 12:42
How would i upload the actual file?
Excelchat Expert
05/09/2018 - 12:42
Upload the file using the Upload button to the right end of the chat input box.
User
05/09/2018 - 12:43
Ok, here it is
[Uploaded an Excel file]
Excelchat Expert
05/09/2018 - 12:43
Good.
User
05/09/2018 - 12:47
Any idea what formula would work best for this?
Excelchat Expert
05/09/2018 - 12:48
First, Which value are we expecting in cell D3
Excelchat Expert
05/09/2018 - 12:48
?
User
05/09/2018 - 12:49
D3 being income rate? This is a percentage of the principal that is being used as projected rental income
Excelchat Expert
05/09/2018 - 12:49
Sorry
Excelchat Expert
05/09/2018 - 12:49
I meant E11
User
05/09/2018 - 12:51
So im starting off in year 1 with 1 rental unit. I want E11 to basically add a rental unit for every $100K in Cumulative Equity. So if the cumulative equity adds $100K in year 2, we would add 1 unit. Or if we added $300K in cumulative equity, we would add 3 units and so on
User
05/09/2018 - 12:52
So E11 based on equity in year 2 should show a value of 2, since we started with one and added $100K in equity
Excelchat Expert
05/09/2018 - 12:56
The cumulative equity for year 3 is:
Excelchat Expert
05/09/2018 - 12:56
= 309,000
Excelchat Expert
05/09/2018 - 12:56
If we divide it by 100,000
Excelchat Expert
05/09/2018 - 12:56
We get
Excelchat Expert
05/09/2018 - 12:57
3 units
Excelchat Expert
05/09/2018 - 12:57
E11 is in year 3, NOT year 2
User
05/09/2018 - 12:58
correct, whereas if equity in year 3 was 600,000, we would have added 4 units from the previous year as opposed to the 1 we would add with equity at $300k
User
05/09/2018 - 12:58
Yes, trying to figure out year 2 and I should be good by applying that for the rest of the cells
Excelchat Expert
05/09/2018 - 01:00
I am not understanding how you come up with the units from the cumulative equity.
User
05/09/2018 - 01:01
Lets use another example to maybe make it simpler - what if i wanted to add a unit for every $10K in revenue (row 12). If I made $10 thousand, id want to add 1 unit. If i made $35K in revenue id want to add 3 units. if i made $74K id want to add 7 units. does that make sense?
Excelchat Expert
05/09/2018 - 01:03
Yeah, up to that point it is clear.
User
05/09/2018 - 01:04
Ok. Any formula you would suggest for this?
User
05/09/2018 - 01:05
I tried an IF function that added a unit if rental income was above 10K, it would add 1 unit, but the problem was if I made $54K it would still only add 1 unit as opposed to the 5 it should have
Excelchat Expert
05/09/2018 - 01:06
Must you use an IF function?
User
05/09/2018 - 01:07
Doesnt have to be an IF function, just thought that might work best until i ran into this problem. Open to any formula that you think would be more appropriate
Excelchat Expert
05/09/2018 - 01:11
This function gives the number of units.
Excelchat Expert
05/09/2018 - 01:11
=INT(C12/10000)
Excelchat Expert
05/09/2018 - 01:12
That is for the case of every unit for Rental income.
Excelchat Expert
05/09/2018 - 01:12
Rental income of $10k
User
05/09/2018 - 01:14
I think that works
User
05/09/2018 - 01:16
Thank you!
Excelchat Expert
05/09/2018 - 01:16
Yeah,
Excelchat Expert
05/09/2018 - 01:16
Is there any question or clarification you may need?
User
05/09/2018 - 01:17
Think I am good for now, thanks so much
Excelchat Expert
05/09/2018 - 01:17
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help.
Excelchat Expert
05/09/2018 - 01:17
Please remember to rate us at the end of the chat for better and quality services. Thank you!
User
05/09/2018 - 01:17
Will do!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
