[SOlVED]LookUp and asign value to the right weeks

Hello Everyone,

I am trying to figure out which formulas should be used in order to get what I want. I am trying to convert a forecast from monthly to weekly. I have the following criterias:

Loan Pay first day of month
Credit Card Pay first day of following month
IT Agreement Pay on the 19th of every month
Cell Phone Pay on the 20th of following month
Insurance Pay on the 26th of every month
Rent Pre-Pay on last day of prior month
Royalty Pay Quarterly of 1st day of the 4th month


I had learned a lot here about LookUp, so I came up with the formula as follow for the loan:

=IF(OR(AND(DAY(D13+2)<=1,DAY(D13+7)>1),AND(DAY(D13)<=1,DAY(D13+7)>1)),LOOKUP(D13-7,'Sheet 1'!$1:$1,('Sheet 1'!$2:$2)),0)

But it doesn't work.

It will be greatly appreciated if someone can give me some hand.

Thanks.

Jackson

See if this will do what you want...

D14 & D16=INDEX($D2:$R2,MATCH(EOMONTH(D$13,-1)+1,$D$1:$R$1,0))
D15&D17=INDEX($D3:$R3,MATCH(EOMONTH(D$13,0)+1,$D$1:$R$1,0))

The other rows would be pretty much a variartion of those 2

Originally Posted by jackson_hollon

...........Please look at my example, use row 14 as example here, you see D14 has $220 ( the week that has the first day of the month), others weeks are blank.

Jackson

I'm officially confused at this point.

"Loan - Pay first day of the month" (row 14) seems clear.....until I start to work on "Credit Card - Pay first day of the following month" (row 15). In the week of 12/28/2014 these are one and the same day, yet the amount for "Credit Card" is a month "behind" the payment for "Loan". I would have expected the amount under 12/28/2014 to be $330....not $300......or if $300 I would expect the "Loan" payment to be $200 instead of the $220 listed. Or the first day of the following month (2/1/2015 ?) would be $363.

Can you help us out on that one?

OK..........this is ugly. I had to resort to CHOOSE in order to keep my thoughts straight. Actually it's not quite as bad as it appears....but could be a nightmare to debug. Fundamentally the first 6 parts of the CHOOSE choices are variations of the same base equation that reconciles where the designated pay dates are. The 7th one.......... the quarterly "Royalty"......... is stand alone. I've included the file, and here is the formula:

Please Login or Register  to view this content.

Sorry for the eye-strain. I opted for Code tags to help organize my thoughts for explanation. Each of the line breaks on INDEX are the start of each successive choice. The SUM line is the unique formula for the quarterly "Royalty" section. I thought about making a separate formula for each row, but that would probably be messier. If that is what you'd like to do instead just copy / paste each section accordingly.

Hope this helps.

Edit: Just copy and paste the formula into D14 fill down and across. It is not an array formula.

Jackson,

Here is the re-work of the formula I alerted you to in PM.

Please Login or Register  to view this content.

This should enable you to extend the formula
indefinitely to the right.....something the former formula would not do. "+(YEAR(D$13)-2015)*12" is the change.

A heads-up is in order here. I don't know how indefinite "indefinitely" is. OFFSET is a volatile function.....it recalculates with each change in the workbook no matter if the change is referenced by OFFSET or not. If these OFFSET calculations have formulas which are dependent on OFFSET's calculations they also recalculate. If these dependencies accumulate you could experience a slow down in workbook performance. Should you experience an unacceptable slow down in your workbook (you might not) these OFFSETs are the likely "culprits". There are usually alternatives to this, but how to do this with the trailing and every increasingly distanced Royalties SUM range is beyond my current capabilities. Just thought you would want to know about that.