Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Formulas and Cumulative Values on a Line Chart
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 structu =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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Min and Max values in Line chart... | Charts and Charting in Excel | |||
How do I add Cumulative values to Stacked Bar chart? | Charts and Charting in Excel | |||
Line chart zero values | Charts and Charting in Excel | |||
line chart with NA() values | Charts and Charting in Excel | |||
bar, line and cumulative bar graph in one chart | Charts and Charting in Excel |