Formulas and Cumulative Values on a Line Chart

[Monomeeth]

Okay, I need some help.

I am using a Line Chart to plot 2 lines: one for cumulative budgeted amount
and the other for cumulative amount actually spent.

All of our expenses etc are recorded in a worksheet called "Expenses
Entered", from which the data comes for calculating the amount actually
spent. To do this I am using the following formula structure:

=SUMPRODUCT(--('Expenses Entered'!A2:A2000>=AQ1),--('Expenses
Entered'!A2:A2000<=AR1),--('Expenses Entered'!F2:F2000="Subscriptions &
Memberships"),'Expenses Entered'!E2:E2000)

In the above formula I am basically getting Excel to add up all the amounts
in Column E if the corresponding expense type in Column F equals
"Subscriptions & Memberships" and if the expense occurred between two dates
as dictated by cells AQ1 and AR1.

So far so good. Now to the problem...

I am pretty much using the same formula in twelve cells, with the only
change being that reference to AR1 will change to AR2 or AR3 and so on up to
AR12, one for each month. A snapshot of the resulting table is below:


MAR APR MAY
JUN JUL
CUMULATIVE BUDGETED AMOUNT $200 $350 $850 $850 $950
CUMULATIVE AMOUNT SPENT $0 $200 $500 $500 $500

Now, the problem I have is that when I view the linked chart, the line
displaying the cumulative amount spent flatlines at $500 up to and including
December. I understand why this is happening, because at the present time
the cumulative amount actually spent is $500. But what I want is for this
particular line in the chart to stop plotting at May because it is the
current month, to stop plotting at June when we're in June, and so on?

What is the easiest way to achieve this?

Thanks,

Joe.

NOTE: This posit also appears in EXCEL PROGRAMMING. I did try and cross-post
this, but obviously got the USENET designation wrong!

 

[Jon Peltier]

If there is an indication of the current month in the sheet, such as blanks
after the current month, you could detect this, and have the formula return
NA() in those cells. This produces an #N/A error which is not plotted in a
line or XY chart.

You might want to adjust your sumproduct so that it reads the category
("Subscriptions & Memberships") from another cell. This way you can adjust
your model by making changes to one cell rather than to a slew of formulas.

- Jon