Updating formula based upon relative reference to last updated row

[kidmullet1]

Hi

I have what I'm sure is a fairly easy question to answer. It relates to updating the cell reference within a formula so that it updates every time a new row is updated.

Now for the hard part - explaining the problem clearly!

I have a budget that I would like to manage evenly over the period of a month (ie spend an equal amount each of 30 days). In order to monitor delivery I have a simple table in which I enter the daily delivery against the date in a separate column. In the next column I simply list numbers 1-30 so I can work out target delivery at each stage. Then a column which simply displays the cumulative delivery. Finally, I have a column showing my average daily spend.

So my question is this - if I get to say the 15th day of a 30 day month and my budget is £30,000, I should have spent £15,000. Therefore, the average daily spend is £1,000 meaning at a daily delivery rate of £1,000 I have 15 days budget remaining.

BUT what if I get to the 15th and I have spent £25,000?
I would like a formula to alert me that if I continue at this avg. daily delivery rate (£1,666 p/d), I only have 3 days worth of budget left!

http://s1179.photobucket.com/albums/x384/happinessstan2/?action=view&current=excel.jpg

I hope I explained myself clearly enough but please let me know if you require any clarification.

Thanks in advance!

 

[circledchicken]

Hi,

Try the following (borrowing from this great tip on referencing the last cell in a column - http://excel.tips.net/T002103_Referencing_the_Last_Cell_in_a_Column.html):

I couldn't quite tell from the image which cells you were using but assuming you have a blank worksheet:

• Cells B6 - B36 contain the numbers 1-31
• Cells C6 - C36 contain dates e.g. 01-Aug-11 to 31-Aug-11
• In D6 enter =TEXT($C6,"ddd") and drag down to D36 (these are the days)
• E6 - E36 are the cells to enter the costs in for each day
• In F6 enter =IF(ISBLANK($E6),#N/A,SUM($E$6:$E6)) and drag down to F36 (this is the cumulative spend)
• In G6 enter =IF(ISBLANK($E6),#N/A,AVERAGE($E$6:$E6)) and drag down to G36 (this is the average daily spend)
• In H6 enter the budget (e.g. £30,000)
• In I6 (the remaining budget) enter =$H$6-INDIRECT("F"&COUNTA($E$6:$E$36)+5)
• In J6 (days left) enter =$I$6/INDIRECT("G"&COUNTA($E$6:$E$36)+5)
• In K6 (days short) enter =COUNTA(INDIRECT("C"&COUNTA($E$6:$E$36)+6):$C$36)-$J$6

To adjust this for a 30 day month, for example, you would delete the contents of cells B36 to G36.