Lookup or Index Match or IF AND?

CTMom

New Member
Joined
Jul 4, 2004
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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.
 
Upvote 0
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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
Back
Top