Happy New Year! I am trying to fit a couple of equations that I need
into one formula and it is givin me some difficulty. To give the
example, I essentially have three equations for rental properties for a
new apartment complex: The steps include 1. How many units are rented
up, 2. How many are currently rented and, 3. The rental price. I have
broken this down into these three steps but combining them is very
difficult for me.

The first step, is how many units are newly rented that month. Say
month 1 is 25, month 2 is 20, month 3 is 10.

The next step is taking the balance of what is rented. Month 1 is 25,
Month 2 is 45 (25 + 20), Month Three is 55 (25+20+10), etc...

Step three is merely multiplying the units rented times the rental
rate.

Combining these is the hard part. The balance currently rented is
calculated by adding the previous month to the current month.(Step One
+ the previous month of Step 2) If I change this to a dollar amount,
(adding step 3), adding the previous month does not work since it is a
dollar amount.

I need to be able to find the balance of units rented without
referencing the previous month. Is this possible? Any suggestions
would be most welcomed!

I'm not sure what you're doing with the information, which makes it
hard to see the best solution.

Let me try to restate your problem:

You have these values:

1. Number of units rented, as of the close of the previous month (call
it PreviousTotalRented)
2. Number of units newly rented this month (call it NewlyRented)
3. The total of 1 and 2 multiplied by the per-unit rent amount.

So you can get #3 by either doing (PreviousTotalRented + NewlyRented) *
Rent Amount or (PreviousTotalRented * Rent Amount) + (NewlyRented *
Rent Amount).

It looks like you answered your own question, unless you're trying to
do something specific that I'm not understanding from your message.
Can you provide more detail?


cardan wrote:
> Happy New Year! I am trying to fit a couple of equations that I need
> into one formula and it is givin me some difficulty. To give the
> example, I essentially have three equations for rental properties for a
> new apartment complex: The steps include 1. How many units are rented
> up, 2. How many are currently rented and, 3. The rental price. I have
> broken this down into these three steps but combining them is very
> difficult for me.
>
> The first step, is how many units are newly rented that month. Say
> month 1 is 25, month 2 is 20, month 3 is 10.
>
> The next step is taking the balance of what is rented. Month 1 is 25,
> Month 2 is 45 (25 + 20), Month Three is 55 (25+20+10), etc...
>
> Step three is merely multiplying the units rented times the rental
> rate.
>
> Combining these is the hard part. The balance currently rented is
> calculated by adding the previous month to the current month.(Step One
> + the previous month of Step 2) If I change this to a dollar amount,
> (adding step 3), adding the previous month does not work since it is a
> dollar amount.
>
> I need to be able to find the balance of units rented without
> referencing the previous month. Is this possible? Any suggestions
> would be most welcomed!

Jeremy, Thanks for the response.

What I am trying to to is get a "Cash Flow" per month on units that are
rented. My spreadsheet goes out over 5 years to show the monthly cash
flow.

Your restatement is pretty much correct. Numbers 1 and 2 are done in
the opposite order. I find the numbers of units rented (NewlyRented)
and this month is then added to the units currently rented
(PreviousTotalRented). The NewlyRented numbers are calculated using a
formula based on dates and the number of units to be rented. so they
are not hard coded.

In order to get the PreviousTotalRented I have to add the current month
+ the last month of the units. This is easily doable if I am talking
only about calculating the units. Finding the balance and adding in the
rental rate is where it gets complicated. For example, Lets say I have
10 units rented (PreviousTotalRented) and the rental rate is $300 per
month. I then multiply to get $3000 for the monthly cash flow.

However, if I add 20 more units the next month, I need to add the last
months units rented (10) with the new 20 units for a total of 30 units
rented. If I was just calculating the Total Units Rented, I would add
the cell togethers to get 30. But, the previous month now says $3000,
instead of 10. This is my dilema. I have tried a few different ways,
but combining these is beyond me. Any suggestions? I hope I explained
it well enough. Let me know yoru thoughts and thanks again for your
time.



Jeremy wrote:
> I'm not sure what you're doing with the information, which makes it
> hard to see the best solution.
>
> Let me try to restate your problem:
>
> You have these values:
>
> 1. Number of units rented, as of the close of the previous month (call
> it PreviousTotalRented)
> 2. Number of units newly rented this month (call it NewlyRented)
> 3. The total of 1 and 2 multiplied by the per-unit rent amount.
>
> So you can get #3 by either doing (PreviousTotalRented + NewlyRented) *
> Rent Amount or (PreviousTotalRented * Rent Amount) + (NewlyRented *
> Rent Amount).
>
> It looks like you answered your own question, unless you're trying to
> do something specific that I'm not understanding from your message.
> Can you provide more detail?
>
>
> cardan wrote:
> > Happy New Year! I am trying to fit a couple of equations that I need
> > into one formula and it is givin me some difficulty. To give the
> > example, I essentially have three equations for rental properties for a
> > new apartment complex: The steps include 1. How many units are rented
> > up, 2. How many are currently rented and, 3. The rental price. I have
> > broken this down into these three steps but combining them is very
> > difficult for me.
> >
> > The first step, is how many units are newly rented that month. Say
> > month 1 is 25, month 2 is 20, month 3 is 10.
> >
> > The next step is taking the balance of what is rented. Month 1 is 25,
> > Month 2 is 45 (25 + 20), Month Three is 55 (25+20+10), etc...
> >
> > Step three is merely multiplying the units rented times the rental
> > rate.
> >
> > Combining these is the hard part. The balance currently rented is
> > calculated by adding the previous month to the current month.(Step One
> > + the previous month of Step 2) If I change this to a dollar amount,
> > (adding step 3), adding the previous month does not work since it is a
> > dollar amount.
> >
> > I need to be able to find the balance of units rented without
> > referencing the previous month. Is this possible? Any suggestions
> > would be most welcomed!

If you're just doing this directly on a worksheet, why don't you try:

Previous New Rental Rate Cash Flow
10 20 $300 (10 + 20) * $300
30 5 $300 (30 + 5) * $300

Unless your NewlyRented formula won't work with that format, that is.
I'd set it up like this:

Assuming your rental rate is a constant number that doesn't vary per
unit and that changes will always apply to all units as of the
effective date of the change, you can just stick it somewhere in the
worksheet by itself and put an absolute reference to it in the formulas
in the table you'll set up.

Next, you set up a table with headings for PreviousTotalRented,
NewlyRented, and MonthlyCashFlow (or whatever you want to call it).

The first PreviousTotalRented needs to be an entered number, and then
you can stick your formula in NewlyRented, and for the MonthlyCashFlow,
it's just (PreviousTotalRented + NewlyRented) * RentalRate.

On the second row of data, and all following rows, you make
PreviousTotalRented equal to the total of PreviousTotalRented and
NewlyRented on the previous line.

This may not be the way you want the information displayed, but once
you have it set up, you can just keep it on a different sheet and have
your "main" sheet refer to it.

So that's my suggestion, unless I'm completely misunderstanding the
problem, still.



cardan wrote:
> Jeremy, Thanks for the response.
>
> What I am trying to to is get a "Cash Flow" per month on units that are
> rented. My spreadsheet goes out over 5 years to show the monthly cash
> flow.
>
> Your restatement is pretty much correct. Numbers 1 and 2 are done in
> the opposite order. I find the numbers of units rented (NewlyRented)
> and this month is then added to the units currently rented
> (PreviousTotalRented). The NewlyRented numbers are calculated using a
> formula based on dates and the number of units to be rented. so they
> are not hard coded.
>
> In order to get the PreviousTotalRented I have to add the current month
> + the last month of the units. This is easily doable if I am talking
> only about calculating the units. Finding the balance and adding in the
> rental rate is where it gets complicated. For example, Lets say I have
> 10 units rented (PreviousTotalRented) and the rental rate is $300 per
> month. I then multiply to get $3000 for the monthly cash flow.
>
> However, if I add 20 more units the next month, I need to add the last
> months units rented (10) with the new 20 units for a total of 30 units
> rented. If I was just calculating the Total Units Rented, I would add
> the cell togethers to get 30. But, the previous month now says $3000,
> instead of 10. This is my dilema. I have tried a few different ways,
> but combining these is beyond me. Any suggestions? I hope I explained
> it well enough. Let me know yoru thoughts and thanks again for your
> time.
>
>
>
> Jeremy wrote:
> > I'm not sure what you're doing with the information, which makes it
> > hard to see the best solution.
> >
> > Let me try to restate your problem:
> >
> > You have these values:
> >
> > 1. Number of units rented, as of the close of the previous month (call
> > it PreviousTotalRented)
> > 2. Number of units newly rented this month (call it NewlyRented)
> > 3. The total of 1 and 2 multiplied by the per-unit rent amount.
> >
> > So you can get #3 by either doing (PreviousTotalRented + NewlyRented) *
> > Rent Amount or (PreviousTotalRented * Rent Amount) + (NewlyRented *
> > Rent Amount).
> >
> > It looks like you answered your own question, unless you're trying to
> > do something specific that I'm not understanding from your message.
> > Can you provide more detail?
> >
> >
> > cardan wrote:
> > > Happy New Year! I am trying to fit a couple of equations that I need
> > > into one formula and it is givin me some difficulty. To give the
> > > example, I essentially have three equations for rental properties for a
> > > new apartment complex: The steps include 1. How many units are rented
> > > up, 2. How many are currently rented and, 3. The rental price. I have
> > > broken this down into these three steps but combining them is very
> > > difficult for me.
> > >
> > > The first step, is how many units are newly rented that month. Say
> > > month 1 is 25, month 2 is 20, month 3 is 10.
> > >
> > > The next step is taking the balance of what is rented. Month 1 is 25,
> > > Month 2 is 45 (25 + 20), Month Three is 55 (25+20+10), etc...
> > >
> > > Step three is merely multiplying the units rented times the rental
> > > rate.
> > >
> > > Combining these is the hard part. The balance currently rented is
> > > calculated by adding the previous month to the current month.(Step One
> > > + the previous month of Step 2) If I change this to a dollar amount,
> > > (adding step 3), adding the previous month does not work since it is a
> > > dollar amount.
> > >
> > > I need to be able to find the balance of units rented without
> > > referencing the previous month. Is this possible? Any suggestions
> > > would be most welcomed!

Hi Jeremy,

The solution you posed is pretty much what I have now. I essentially
have steps 1 and 2 on another sheet and these are what I am referring
to in the worksheet. I was hoping for a formula that would wrap all
these steps into one formula. The apartment projects are very dynamic
and the calcs can change from rents to sales and are based on a "per
building". (I have the sales formula). I did not mention this before
because this is the only part I am stuck on.
With these potential changes, it would be easier to have the formula in
one row, rather than referencing someplace else. It may be easier if I
sent you the page from the workbook to show the formulas and the
dilema. Is this something you would be willing to look at? Thanks
again for your response and input.



Jeremy wrote:
> If you're just doing this directly on a worksheet, why don't you try:
>
> Previous New Rental Rate Cash Flow
> 10 20 $300 (10 + 20) * $300
> 30 5 $300 (30 + 5) * $300
>
> Unless your NewlyRented formula won't work with that format, that is.
> I'd set it up like this:
>
> Assuming your rental rate is a constant number that doesn't vary per
> unit and that changes will always apply to all units as of the
> effective date of the change, you can just stick it somewhere in the
> worksheet by itself and put an absolute reference to it in the formulas
> in the table you'll set up.
>
> Next, you set up a table with headings for PreviousTotalRented,
> NewlyRented, and MonthlyCashFlow (or whatever you want to call it).
>
> The first PreviousTotalRented needs to be an entered number, and then
> you can stick your formula in NewlyRented, and for the MonthlyCashFlow,
> it's just (PreviousTotalRented + NewlyRented) * RentalRate.
>
> On the second row of data, and all following rows, you make
> PreviousTotalRented equal to the total of PreviousTotalRented and
> NewlyRented on the previous line.
>
> This may not be the way you want the information displayed, but once
> you have it set up, you can just keep it on a different sheet and have
> your "main" sheet refer to it.
>
> So that's my suggestion, unless I'm completely misunderstanding the
> problem, still.
>
>
>
> cardan wrote:
> > Jeremy, Thanks for the response.
> >
> > What I am trying to to is get a "Cash Flow" per month on units that are
> > rented. My spreadsheet goes out over 5 years to show the monthly cash
> > flow.
> >
> > Your restatement is pretty much correct. Numbers 1 and 2 are done in
> > the opposite order. I find the numbers of units rented (NewlyRented)
> > and this month is then added to the units currently rented
> > (PreviousTotalRented). The NewlyRented numbers are calculated using a
> > formula based on dates and the number of units to be rented. so they
> > are not hard coded.
> >
> > In order to get the PreviousTotalRented I have to add the current month
> > + the last month of the units. This is easily doable if I am talking
> > only about calculating the units. Finding the balance and adding in the
> > rental rate is where it gets complicated. For example, Lets say I have
> > 10 units rented (PreviousTotalRented) and the rental rate is $300 per
> > month. I then multiply to get $3000 for the monthly cash flow.
> >
> > However, if I add 20 more units the next month, I need to add the last
> > months units rented (10) with the new 20 units for a total of 30 units
> > rented. If I was just calculating the Total Units Rented, I would add
> > the cell togethers to get 30. But, the previous month now says $3000,
> > instead of 10. This is my dilema. I have tried a few different ways,
> > but combining these is beyond me. Any suggestions? I hope I explained
> > it well enough. Let me know yoru thoughts and thanks again for your
> > time.
> >
> >
> >
> > Jeremy wrote:
> > > I'm not sure what you're doing with the information, which makes it
> > > hard to see the best solution.
> > >
> > > Let me try to restate your problem:
> > >
> > > You have these values:
> > >
> > > 1. Number of units rented, as of the close of the previous month (call
> > > it PreviousTotalRented)
> > > 2. Number of units newly rented this month (call it NewlyRented)
> > > 3. The total of 1 and 2 multiplied by the per-unit rent amount.
> > >
> > > So you can get #3 by either doing (PreviousTotalRented + NewlyRented) *
> > > Rent Amount or (PreviousTotalRented * Rent Amount) + (NewlyRented *
> > > Rent Amount).
> > >
> > > It looks like you answered your own question, unless you're trying to
> > > do something specific that I'm not understanding from your message.
> > > Can you provide more detail?
> > >
> > >
> > > cardan wrote:
> > > > Happy New Year! I am trying to fit a couple of equations that I need
> > > > into one formula and it is givin me some difficulty. To give the
> > > > example, I essentially have three equations for rental properties for a
> > > > new apartment complex: The steps include 1. How many units are rented
> > > > up, 2. How many are currently rented and, 3. The rental price. I have
> > > > broken this down into these three steps but combining them is very
> > > > difficult for me.
> > > >
> > > > The first step, is how many units are newly rented that month. Say
> > > > month 1 is 25, month 2 is 20, month 3 is 10.
> > > >
> > > > The next step is taking the balance of what is rented. Month 1 is 25,
> > > > Month 2 is 45 (25 + 20), Month Three is 55 (25+20+10), etc...
> > > >
> > > > Step three is merely multiplying the units rented times the rental
> > > > rate.
> > > >
> > > > Combining these is the hard part. The balance currently rented is
> > > > calculated by adding the previous month to the current month.(Step One
> > > > + the previous month of Step 2) If I change this to a dollar amount,
> > > > (adding step 3), adding the previous month does not work since it is a
> > > > dollar amount.
> > > >
> > > > I need to be able to find the balance of units rented without
> > > > referencing the previous month. Is this possible? Any suggestions
> > > > would be most welcomed!