freezing formula values after current date

[ben]

Hello.

I have a spreadsheet I use to work out my finances which works great for me.
Down one column I have every day in the year and each day's row has an entry
for how much is in my account and how much is effectively there after
deducting checks that haven't cleared and other types of holds on my
account. I can enter in the remainder of the cells in each row different
bills or whatever that may be coming out that day (these are deducted from
the effective cash cell for that row too).

One down side to the sheet is that in working out how much is effectively in
my account I deduct from a different sheet all checks and holds that happen
to be there which works great for planning finances from now and in the
future, but all the other rows prior to that become unreliable because the
same checks are deducted from them too so they don't reflect what was
happening then. I am wondering if there is any way I can freeze the last
calculated value for a date that is before todays date so that after that
day has passed its value no longer changes based on the different checks and
other types of holds that come in and out?

Thanks.

 

[Bernie Deitrick]

ben,

Use a formula like

=SUMPRODUCT((A2:A1000<TODAY())*(C2:C1000 = "Cleared")*(D21000))

But a better way would be to use a single row for every transaction, each
with a date, type, status (pending, planned, cleared), amount (plus for
deposits, negative for payments), etc. and then simply use that for the
basis of a pivot table.

HTH,
Bernie
MS Excel MVP

 

[ben]

Hello,

The way the sheet is organized, each row has the following headings:
day, date, plan, actual, eff. cash, expenses (cols G-O)
where day is the day of the week based on the date. Plan has amounts coming
into the account on a particular date, actual contains the amount in the
account on that day, eff cash takes the actual amount if one was entered or
the previous eff cash and subtracts that days expenses (cols G to O) and any
holds (checks that have been written and have not been cashed yet and the
like) which are specified in a hold sheet containing cols for chq#, amt,
recipient and date. The holds sheet messes all the past rows up since every
row's eff cash entry deducts the sum of everything contained in the holds
sheet which is constantly changing. This works for planning for the future
as whatever is there now is the basis for the future balances in the account
(and this is the main use for the spreadsheet), but prior rows are messed up
since they need to be based on whatever the sum of the holds was up to that
row/day which I cannot know. I actually arrange the sheet in to squares
containing 2 rows for each day (the second row containing descriptions) as
follows for example:
day, date, plan, actual, eff cash, expenses...
Thu, 6-Mar-08 $1,448.26, $ 157.01

Fri, 7-Mar-08, $1,315.78, $2,746.04, $1,345.17, $109.62
Pay Water Bill
Sat, 8-Mar-08, $1,345.17,

The holds contains $1,291.25 worth of checks and the eff cash reflects that.
But if some of the checks clear and I remove them, the eff cash amounts will
increase even though Mar 6th contained $157.01. Similarly if some checks are
written out and the holds sheet contains more than $1,291.25 then eff cash
for Mar 6th will decrease. I have ignored this behavior until now but if
there were a way to freeze the values somehow at the right time, that would
be perfect.