Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining a 3-Step Formula into One Formula
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining a 3-Step Formula into One Formula
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining a 3-Step Formula into One Formula
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining a 3-Step Formula into One Formula
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining a 3-Step Formula into One Formula
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please can you help. I can not remember what the special formula step is. | Excel Discussion (Misc queries) | |||
One more step in the Formula Please | Excel Worksheet Functions | |||
How to change step in formula | Excel Worksheet Functions | |||
I can see formula result when I step into it but not in the cell | Excel Discussion (Misc queries) | |||
Automatically step a formula? | Excel Worksheet Functions |