Excel - IF Function Problem - Expert Solution

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
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc