• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Continuity - Selecting Value based on range of dates (that change)

yhernandez

New Member
Here is the use case.

A have column headings that are dated friday of each week. Each friday, I updated the first column heading, and the rest ones update themselves into the forecast.

I'm doing a cashflow forecast. Based on the week/month, I apply certain costs to that column. I pull data from other tabs. Here is what I want to add.

Let's say we have a recurring expense $25,000 that occurs on the 15th and 31st every month. I want to do an If statement or Lookup that populates the cell if that column is the closest column to the 15th of the month (but not over), and the end of the month (but not over). As you update the sheet each week and advance the date, the historical numbers fall off. I've included two screenshots to simulate what I am trying to do.

Thanks,

Any pointers/ideas suggestions?
 

Attachments

yhernandez

New Member
Sure. It is attached. The date calculation needs to be relative so that as we update the interval it can move w/it. In sample spreadsheet, the dollar amounts are hard coded. Thats where the formulas should ideally go.
 

Attachments

yhernandez

New Member
It is indeed. Im trying this route:

=IF(MATCH((EOMONTH(G3,-1)+15),G3),$E$25,IF(MATCH((EOMONTH(G3,0)),G3),$E$25,"NA"))

But not really working out.
 
Last edited:
Top