Lookup or Index Match or IF AND?

[CTMom]

I need to create a schedule of recurring expense payments on a weekly basis. Each expense is payable on a certain day of the month (eg. rent for property 1 due on the 1st, rent for property 2 due on the 10th). I want to then automatically populate a weekly cashflow schedule so that the amount of the rent is inserted in correct week. Eg. Property 1 rent goes in week where the 1st of the month falls.

See pasted spreadsheet.

I would appreciate any advice on best way to do this.
Thanks

Excel Workbook
	A	B	C	D	E	F	G	H	I
8	Category	Paid on Day of Mth	Amount	*	*	*	*	*	*
9	Rent 1	1	1500	*	*	*	*	*	*
10	Rent 2	15	2500	*	*	*	*	*	*
11	Rent 3	1	2000	*	*	*	*	*	*
12	Rent 4	10	3000	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*
14	*	1/01/2011	8/01/2011	15/01/2011	22/01/2011	29/01/2011	5/02/2011	12/02/2011	19/02/2011
15	Day Number	* * * * * * * * * * * * * * * * * * * * * *1	* * * * * * * * 8	* * * * * * * * 15	* * * * * * * * 22	* * * * * * * * 29	* * * * * * * * 5	* * * * * * * * 12	* * * * * * * * 19
16	Rent 1	*	*	*	*	*	*	*	*
17	Rent 2	*	*	*	*	*	*	*	*
18	Rent 3	*	*	*	*	*	*	*	*
19	Rent 4	*	*	*	*	*	*	*	*
Sheet2

 

[DonkeyOte]

You have a few options I guess... one possibility:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
8	Category	Paid on Day of Mth	Amount
9	Rent 1	1	1500
10	Rent 2	15	2500
11	Rent 3	30	2000
12	Rent 4	10	3000
13
14		01/01/2011	08/01/2011	15/01/2011	22/01/2011	29/01/2011	05/02/2011	12/02/2011	19/02/2011	26/02/2011
15	Day Number	1	8	15	22	29	5	12	19	26
16	Rent 1	1500	0	0	0	1500	0	0	0	1500
17	Rent 2	0	0	2500	0	0	0	2500	0	0
18	Rent 3	0	0	0	0	2000	0	0	0	2000
19	Rent 4	0	3000	0	0	0	3000	0	0	0
Sheet1

but I'm sure with more thought / brain power the above can be simplified

 

[CTMom]

THANK YOU!!!

I knew I loved this Board. It is amazing.

I would never have figured this out and it works perfectly.

Thank you from the other side of the world.

 

[CTMom]

Hi, I just noticed that the formula does not work across all weeks - it is adding in expense in wrong week. Please see below and see if you can figure out problem.

Thanks

Excel Workbook
	C	D	E	F	G	H	I	J	K
29	Category	Paid on Day of Mth	Amount
30	Rent 1	1	1500
31	Rent 2	15	2500
32	Rent 3	1	2000
33	Rent 4	10	3000
34
35		1/01/2011	8/01/2011	15/01/2011	22/01/2011	29/01/2011	5/02/2011	12/02/2011	19/02/2011
36	Day Number	1	8	15	22	29	5	12	19
37	Rent 1	1500	0	0	0	1500	0	0	0
38	Rent 2	0	0	2500	0	0	0	2500	0
39	Rent 3	2000	0	0	0	2000	0	0	0
40	Rent 4	0	3000	0	0	0	3000	0	0
Recurring Wkly

 

[DonkeyOte]

I presumed the week headers were week commencing rather than week ending dates - this was not specified

Given the fact you say the results above are incorrect I presume you are saying that the dates are week ending dates, is that correct ?

When posting Jeanie screenshots you don't need to post all the formulas - set the Analyse Range to include a select few (as deemed necessary)
there's little value in repeating all of the formulas if they are consistent

 

[DonkeyOte]

I just looked at this again and given the dates specified are Sat it would seem you are using week ending rather than commencing - in truth I should have checked earlier !

B23:
=IF(MEDIAN(B$15-6,B$15,$B9)=$B9,$C9,IF(AND(B$15<6,OR(B$15>=$B9,DAY(B$14-7)<$B9)),$C9,0))

Hopefully the above revision will suffice - others may have more elegant alternatives.

 

[CTMom]

Thanks - works great.

Am novice with Excel Jeanie so just put it all in - sorry.

Can I ask how formula would change if I did this on daily schedule based on weekdays only?

Thanks for all your help

 

[DonkeyOte]

Not sure I follow...are you saying horizontal axis would be Mon-Fri dates rather than week ending dates ?

If so, were the payment day to fall on a weekend or exceed the no. of days in month (eg 31 in April) in which day should the payment be placed ?

Best to post an example with some manually calculated expected results.

 

[CTMom]

Yes you are correct - M-F on horizontal axis. If payment falls on a weekend then pay should be on Friday prior.

Excel Workbook
	A	B	C	D	E	F	G	H	I
8	Category	Paid on Day of Mth	Amount
9	Rent 1	6	1500
10	Rent 2	15	2500
11	Rent 3	8	2000
12	Rent 4	12	3000
13
14	Weekday	4/03/2011	7/03/2011	8/03/2011	9/03/2011	10/03/2011	11/03/2011	14/03/2011	15/03/2011
15	Day Number	4	7	8	9	10	11	14	15
16	Rent 1	1500
17	Rent 2								2500
18	Rent 3			2000
19	Rent 4						3000
Sheet1