how do I set monthly totals, but keep future months at zero?

**Neutrino Two** · 12-15-2011, 04:27 PM

I'v made a sheet for monthly Bar Graph statistics,
which shows:
the month TOTAL customers.
alongside that it has new customers and closed accounts.

so we have for example

total closed acc's new acc's
Jan 200 8 12
Feb 0 0

with each month bar showing the 3 totals.
I'v added calculations so that the
next month = last month total, minus current month closed, plus current month new.

and as each month comes and goes, I update the closed and new accounts,
which will then refect in the months total.
I'd like to be able to have Future "month Total" showing zero...
if it's March, I'd like April onward to be showing zero...
but of course having inserted the Calculaton as I have... that doesnt seem possible.
Or Is It? I suspect not, doing it the way I am.
and

if there is a simple way.. it just might to too early in the AM at present for me to see it

Thanks

**TMS** · 12-15-2011, 04:38 PM

You might want to share a sample workbook with your formula and data layout.

Regards, TMS

**tuph** · 12-15-2011, 08:12 PM

I insert a heading in all of my worksheets that includes the start and finish dates of the data, then I can use an IF statement for this purpose. For example, if you enter the end date of the date range in cell D4 and your list of data starts in row 10, your formula would like like this: =IF(A10>G4,0,lastmonthtotal-currentmonthclosed+currentmonthnew)

holin_168 · 12-16-2011, 02:59 AM

You next time, better post an attached file so more easy for us to provide some solution.

To do this you might need another cell to state where you are now. So I create report date which an automatic update base on your computer date (in C1) or you might prefer to input it. Please follow my link for what i have been done for you...

http://msaccessexcell.blogspot.com/2...keep.html#more