Hi Gary,
Thank you - this works, partly.
There could be instances however where the rental effective date is
not the 1st of every month (could be the 2nd), & the precision
required will not be met. Under-estimating the rent payable over the
10,000 odd contracts could be potentially a major cash flow problem.
Maybe there's a workaround?
Many thanks once again
Neil
On Mar 26, 12:09*am, Gary Brown <junk_at_kinneson_dot_com> wrote:
> Assumptions:
> - Headings are in row 1
> - Data starts in row 2
> - Col A = Contract Code
> - Col B = Rent Amount
> - Col C = effective date (1st of month date)
> - Col D -= frequency of pymt
> - Col E thru Col AB = Month Headings - Apr-09 thru Mar-11
> * * - Month Headings are actually dates (1st of the month)
> * * * * *- i.e. Apr-09 is actually 01.04.2009 /
> * * * * * * * * *May-09 is actually 01.05.2009, etc
> - Analysis ToolPak is installed
> [This is an addin that comes with Excel 2003 and lower but is usually not
> activated. *It is a part of Excel 2007. *If this addin is not installed, the
> formula below will give the #NAME? error because the EoMonth( ) function is
> used. *To install the addin, go to TOOLS > ADD-INS... and check Analysis
> ToolPak.]
>
> In cell E2, put the formula...
> =IF(AND(E$1>=$C2,E$1<=EOMONTH($C2,$D2-1)),$B2,0)
>
> Copy this formula down and across. *It is recommended to format it withthe
> 'comma' format so that if the result = 0, a dash ( - ) will appear.
>
> --
> Hope this helps. *
> If it does, please click the Yes button.
> Thanks in advance for your feedback.
> Gary Brown
>
>
>
> "Neil" wrote:
> > Hi,
>
> > 1. I have a list of about 10K contracts with amounts & effective dates
> > from which rents are to be paid.
> > 2. These dates range between 01 April 2009 & 31 March 2011.
> > 3. The list has a column of frequency of payments (between 1 & 12),
> > which means that rent is to be paid once or 12 times.
>
> > I have to find out the total monthly rental payable - from 01 April
> > 2009 until 31 March 2011.
>
> > The table looks something like this
>
> > ContractCode * *RentAmount * RentEffectiveFrom * *Frequency
> > 1 * * * * * * * * * * *1000 * * * * * * * 01.05.2009 * * * * * * *3
> > 1 * * * * * * * * * * *1100 * * * * * * * 01.08.2009 * * * * * * *3
> > 1 * * * * * * * * * * * 1200 * * * * * * *01.11.2009 * * * * * * *3
> > 2 * * * * * * * * * * *100 * * * * * * * * 01.04.2009 * * * * * * 12
> > 2 * * * * * * * * * * * 120 * * * * * * * *01.04.2010 * * * * * * *12
>
> > The result expected is something like this
>
> > CCode * * * * * * *Apr09 *May09 * Jun09 * *Jul09 * Aug09 * Sep09
> > Oct09 *Nov09 Dec09 Jan09 Feb09 Mar09 Apr10
> > 1 * * * * * * * * * * * * * * * * 1000 * * 1000 * * *1000
> > 1
> > 1000 * *1100 * *1100
> > 1
> > 1200 * 1200 * 1200
>
> > 2 * * * * * * * * * * *100 * * * 100 * * *100 * * * * * 100 *100
> > 100 until Mar09
> > 2
> > Apr10 to Mar11
>
> > I'm certain this is possible with either VBA or with formulas - can
> > you please help?
>
> > Thanks
> > .- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
