|
help with Sum in between dates
Hi, I have the following numbers, about 4 real estate projects (named
A, B, C, D). For each of them, there is a lease contract, and the rent changes according to a "start date" and an "end date". But since I need to sum the rent paid in each year, I need for each project the sum of rent paid in that specific year, considering the rent amount can change during the year. In the example below, project A would pay 2 months of first due rent in year 2011, then in 2012 it will pay for 10 months the same rent, and then the new rent for the next 2 months, and so on. How to do these total? thanks Projects START END ANNUAl RENT A 01/11/2011 31/10/2012 1.800.000 A 01/11/2012 31/10/2013 2.200.000 A 01/11/2013 31/10/2014 2.300.000 A 01/11/2014 30/12/2021 2.800.000 B 01/01/2012 30/09/2013 1.800.000 B 01/10/2013 30/12/2021 3.600.000 C 01/11/2011 30/12/2021 2.600.000 D 01/01/2012 31/12/2013 2.000.000 D 01/01/2014 31/12/2015 2.400.000 D 01/01/2016 30/12/2021 2.800.000 |
help with Sum in between dates
On Nov 16, 10:23*am, via sarpi wrote:
Hi, I have the following numbers, about 4 real estate projects (named A, B, C, D). For each of them, there is a lease contract, and the rent changes according to a "start date" and an "end date". But since I need to sum the rent paid in each year, I need for each project the sum of rent paid in that specific year, considering the rent amount can change during the year. *In the example below, project A would pay 2 months of first due rent in year 2011, then in 2012 it will pay for 10 months the same rent, and then the new rent for the next 2 months, and so on. How to do these total? thanks Projects START * * * * *END * * * * * * * * * * ANNUAl RENT A * * * 01/11/2011 * * *31/10/2012 * * * 1.800.000 A * * * 01/11/2012 * * *31/10/2013 * * * 2.200.000 A * * * 01/11/2013 * * *31/10/2014 * * * 2.300.000 A * * * 01/11/2014 * * *30/12/2021 * * * 2.800.000 B * * * 01/01/2012 * * *30/09/2013 * * * 1.800.000 B * * * 01/10/2013 * * *30/12/2021 * * * 3.600.000 C * * * 01/11/2011 * * *30/12/2021 * * * 2.600.000 D * * * 01/01/2012 * * *31/12/2013 * * * 2.000.000 D * * * 01/01/2014 * * *31/12/2015 * * * 2.400.000 D * * * 01/01/2016 * * *30/12/2021 * * * 2.800.000 Your question needs better clarification. |
help with Sum in between dates
Your question needs better clarification. sorry for this. So I have 4 real estate projects, for each of them there is a lease in place, where a tenant pays a rent. The problem is that this rent might change at any date, within the year. In the example above, for the project A, tenant pays 1800m from 1/11/2011 to 31/10/2012. This means that in 2012 tenant pays for the first 10 months= (1800/12)*10. Then for the last 2 months of 2012 rent steps up to (2200/12) per month (second row in the example above). And so on. Rent might change/step up without any fixed rule. I need a formula which gives me the total paid for each project in a specific year. So the formula would check how many months for the year in question tenant pays a specific bracket (in the example above, for 2012 tenant pays first bracket for 10 months, and second bracket for 2 months) and sum the result. I believe is a sort of Sumproduct IF the bracket falls within the year I am summing up, or something like this. Hope this is clearer now thanks a lot in advance cheers |
help with Sum in between dates
On Nov 16, 5:11*pm, via sarpi wrote:
Your question needs better clarification. sorry for this. So I have 4 real estate projects, for each of them there is a lease in place, where a tenant pays a rent. The problem is that this rent might change at any date, within the year. In the example above, for the project A, tenant pays 1800m from 1/11/2011 to 31/10/2012. This means that in 2012 tenant pays for the first 10 months= (1800/12)*10. Then for the last 2 months of 2012 rent steps up to (2200/12) per month (second row in the example above). And so on. Rent might change/step up without any fixed rule. I need a formula which gives me the total paid for each project in a specific year. *So the formula would check how many months for the year in question tenant pays a specific bracket (in the example above, for 2012 tenant pays first bracket for 10 months, and second bracket for 2 months) and sum the result. I believe is a sort of Sumproduct IF the bracket falls within the year I am summing up, or something like this. Hope this is clearer now thanks a lot in advance cheers Instead of recreating your project, just send this and your file with examples to dguillett1 @gmail.com |
help with Sum in between dates
hi,
create a new table F2: F5 put the values (A, B, C, D) G1: Q1 put the values (2011, 2012, 2013, etc ...) in cell G2 put the following formula: =SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($B$2:$B$11)=G$1)*(13-MONTH($B$2:$B$11))*($D$2:$D$11)) +SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($C$2:$C$11)=G$1)*(13-MONTH($C$2:$C$11))*($D$2:$D$11)) and copy this formula in range G2:Q5 -- isabelle Le 2011-11-16 18:11, via sarpi a Γ©crit : Your question needs better clarification. sorry for this. So I have 4 real estate projects, for each of them there is a lease in place, where a tenant pays a rent. The problem is that this rent might change at any date, within the year. In the example above, for the project A, tenant pays 1800m from 1/11/2011 to 31/10/2012. This means that in 2012 tenant pays for the first 10 months= (1800/12)*10. Then for the last 2 months of 2012 rent steps up to (2200/12) per month (second row in the example above). And so on. Rent might change/step up without any fixed rule. I need a formula which gives me the total paid for each project in a specific year. So the formula would check how many months for the year in question tenant pays a specific bracket (in the example above, for 2012 tenant pays first bracket for 10 months, and second bracket for 2 months) and sum the result. I believe is a sort of Sumproduct IF the bracket falls within the year I am summing up, or something like this. Hope this is clearer now thanks a lot in advance cheers |
help with Sum in between dates
sorry,
you have to remove the second "13-" =SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($B$2:$B$11)=G$1)*(13-MONTH($B$2:$B$11))*($D$2:$D$11)) +SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($C$2:$C$11)=G$1)*(MONTH($C$ 2:$C$11))*($D$2:$D$11)) -- isabelle Le 2011-11-17 09:39, isabelle a Γ©crit : hi, create a new table F2: F5 put the values (A, B, C, D) G1: Q1 put the values (2011, 2012, 2013, etc ...) in cell G2 put the following formula: =SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($B$2:$B$11)=G$1)*(13-MONTH($B$2:$B$11))*($D$2:$D$11)) +SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($C$2:$C$11)=G$1)*(13-MONTH($C$2:$C$11))*($D$2:$D$11)) and copy this formula in range G2:Q5 |
help with Sum in between dates
thanks really a lot....BUT it doesnt work.
take for example the Project "C". your formula dont get it, since its start-year is before our calculation date, and its end-date is after. This means the rent is always the same in the years between START and END, but your formula (Year = ...) don't consider it |
help with Sum in between dates
On Wed, 16 Nov 2011 08:23:07 -0800 (PST), via sarpi wrote:
Hi, I have the following numbers, about 4 real estate projects (named A, B, C, D). For each of them, there is a lease contract, and the rent changes according to a "start date" and an "end date". But since I need to sum the rent paid in each year, I need for each project the sum of rent paid in that specific year, considering the rent amount can change during the year. In the example below, project A would pay 2 months of first due rent in year 2011, then in 2012 it will pay for 10 months the same rent, and then the new rent for the next 2 months, and so on. How to do these total? thanks Projects START END ANNUAl RENT A 01/11/2011 31/10/2012 1.800.000 A 01/11/2012 31/10/2013 2.200.000 A 01/11/2013 31/10/2014 2.300.000 A 01/11/2014 30/12/2021 2.800.000 B 01/01/2012 30/09/2013 1.800.000 B 01/10/2013 30/12/2021 3.600.000 C 01/11/2011 30/12/2021 2.600.000 D 01/01/2012 31/12/2013 2.000.000 D 01/01/2014 31/12/2015 2.400.000 D 01/01/2016 30/12/2021 2.800.000 Assuming your table above starts in A1, NAME the ranges Select the table Defined Names: Create from selection / top row You will then have four names like: ANNUAL_RENT =Sheet1!$D$2:$D$11 END =Sheet1!$C$2:$C$11 Projects =Sheet1!$A$2:$A$11 START =Sheet1!$B$2:$B$11 Then, set up a new table: I1: A J1: B K1: C L1: D H2: 2011 .... ... H12: 2012 This formula must be **array-entered**: I2: =SUMPRODUCT((Projects=I$1)*(TRANSPOSE( DATE($H2,ROW(INDIRECT("1:12")),1))=START)* (TRANSPOSE(DATE($H2,ROW( INDIRECT("1:12")),1))<=END)*ANNUAL_RENT/12) ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. Finally, select the cell and fill right to L2 Then select I2:L2 and fill down to I12:L12 This is what I get: A B C D 2011 $300.00 $- $433.33 $- 2012 $1,866.67 $1,800.00 $2,600.00 $2,000.00 2013 $2,216.67 $2,250.00 $2,600.00 $2,000.00 2014 $2,383.33 $3,600.00 $2,600.00 $2,400.00 2015 $2,800.00 $3,600.00 $2,600.00 $2,400.00 2016 $2,800.00 $3,600.00 $2,600.00 $2,800.00 2017 $2,800.00 $3,600.00 $2,600.00 $2,800.00 2018 $2,800.00 $3,600.00 $2,600.00 $2,800.00 2019 $2,800.00 $3,600.00 $2,600.00 $2,800.00 2020 $2,800.00 $3,600.00 $2,600.00 $2,800.00 2021 $2,800.00 $3,600.00 $2,600.00 $2,800.00 Note that time periods before the first date in START for a given project are NOT included. |
help with Sum in between dates
hi,
ok i see what you want to say but i see no other solution than use a VBA macro, is what you want to do ? -- isabelle Le 2011-11-18 09:33, via sarpi a ιcrit : thanks really a lot....BUT it doesnt work. take for example the Project "C". your formula dont get it, since its start-year is before our calculation date, and its end-date is after. This means the rent is always the same in the years between START and END, but your formula (Year = ...) don't consider it |
help with Sum in between dates
Ron got it right!
thanks a lot! |
help with Sum in between dates
Ron,
what's the point of using Transpose? doesn't the array have the same dimension even without transposing? if I get it properly, the formula Ron suggests basically creates a monthly schedule within the formula itself. Pretty smart! |
help with Sum in between dates
On Sat, 19 Nov 2011 07:15:32 -0800 (PST), via sarpi wrote:
Ron, what's the point of using Transpose? doesn't the array have the same dimension even without transposing? Yes, but you need to multiply each element in each array by the other; if you don't transpose then you will only do a one-to-one, rather than a one-to-all. Like multiplying two arrays of columns, vs an array of row by an array of columns. |
help with Sum in between dates
On Sat, 19 Nov 2011 07:01:09 -0800 (PST), via sarpi wrote:
Ron got it right! thanks a lot! Glad to help. Thanks for the feedback. |
| All times are GMT +1. The time now is 11:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com