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