Sunday, June 9, 2013

yogi_Compute Item By Item Average Of Budgeted Items And Chart For Remaining Days Of Month

                                          Google Spreadsheet   Post  #1231
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jun 09, 2013

Date-based Formula for Budget Doc

I have a personal budget, and I'm trying to include a row of cells which will tell me, given the remainder for each spending pot, how much I can spend as a maximum for each of the remaining days in the month.

Here's a simple example:

I have $10 designated for food over 5 days, that means a maximum daily spend of $2. 
But say I don't spend anything for the first day, that means the new daily maximum for the remaining 4 days is $2.5

Basically I want a row of cells to give me that data for my entire budget for each of my spending pots.

Right now, row 36 serves this function, but only in a very crude way. 
It counts the number of cells in its column which contain "0" and then divides the remainder in that spending pot by the number of cells with "0". 

In some cases, because I spend something on, say, food everyday, this works well...but for other categories, because I can go days at a time without spending anything, the formula counts "0"s which represent days which have already passed, giving me an underestimated daily max. spend.

Does anyone know of a way I can provide a daily max. spend number which takes only the remaining days in the month into account?

Any help would be appreciated.