Lookup or Index Match or IF AND?

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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
ABCDEFGHI
8CategoryPaid on Day of MthAmount******
9Rent 111500******
10Rent 2152500******
11Rent 312000******
12Rent 4103000******
13*********
14*1/01/20118/01/201115/01/201122/01/201129/01/20115/02/201112/02/201119/02/2011
15Day Number* * * * * * * * * * * * * * * * * * * * * *1* * * * * * * * 8* * * * * * * * 15* * * * * * * * 22* * * * * * * * 29* * * * * * * * 5* * * * * * * * 12* * * * * * * * 19
16Rent 1********
17Rent 2********
18Rent 3********
19Rent 4********
Sheet2
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You have a few options I guess... one possibility:

Excel Workbook
ABCDEFGHIJ
8CategoryPaid on Day of MthAmount
9Rent 111500
10Rent 2152500
11Rent 3302000
12Rent 4103000
13
1401/01/201108/01/201115/01/201122/01/201129/01/201105/02/201112/02/201119/02/201126/02/2011
15Day Number181522295121926
16Rent 1150000015000001500
17Rent 2002500000250000
18Rent 3000020000002000
19Rent 4030000003000000
Sheet1


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

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
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

New Member
Joined
Jul 4, 2004
Messages
32

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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 !

Code:
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

New Member
Joined
Jul 4, 2004
Messages
32

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

New Member
Joined
Jul 4, 2004
Messages
32
Yes you are correct - M-F on horizontal axis. If payment falls on a weekend then pay should be on Friday prior.

Excel Workbook
ABCDEFGHI
8CategoryPaid on Day of MthAmount
9Rent 161500
10Rent 2152500
11Rent 382000
12Rent 4123000
13
14Weekday4/03/20117/03/20118/03/20119/03/201110/03/201111/03/201114/03/201115/03/2011
15Day Number478910111415
16Rent 11500
17Rent 22500
18Rent 32000
19Rent 43000
Sheet1
 

Forum statistics

Threads
1,141,286
Messages
5,705,517
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top