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!
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!