Formulas and Cumulative Values on a Line Chart

M

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 one 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: I will try and cross-post this to the Charts discussions group - time
will tell if this worked!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top