Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Historical Data - Progeress Graph - Track Changes
Hi
I would like some help on how to make historical data - progress mesurement in the following case: SHEET ONE - TASKLIST A B C ID STATUS STATUS CHANGED ------------------------------------------------ 1 New 2006-10-28 2 Working 2006-10-29 3 Working 2006-11-01 SHEET TWO - GRAPH DATA A B C D Date New Working Closed 2006-10-28 1 0 0 2006-10-29 1 1 0 2006-10-30 1 1 0 2006-11-01 1 2 0 2006-11-02 #NA #NA #NA ToDay = 2006-11-01 I am using a formula like the one below today: =IF('Graf Data'!$A3<=TODAY();IF(TaskList!$C$5:$C$317<='Graf Data'!$A3;SUM(IF(IssueLog!$B$5:$B$317='Graf Data'!B$1;1;0);0);0);NA()) This formula almost does the task, but it has a problem when for example New above is changed to Working (data i automatical set to current date) all old (historical) data for col B in GrafData is set to 0 - old data is overwritten. The Working Col. is updated from 2 to 3 as wanted with hist. data intact. Any help on this problem would be much appriciated ---------------------- "vezerid" wrote: Steen, in B2 of your target sheet you can use the following formula and copy down and across as necessary: =SUMPRODUCT((TaskList!$B$5:$B$317=B$1)*(TaskList!$ C$5:$C$317=$A2)) HTH Kostis Vezerides -------------------- Hi Thanks for your answer. I have tryed to fool a little around with your suggestion, but it seems to have the same problem as my formula - just a little smarter :-) The problem is still that all "old" data is change when changing the status. It's a bit dificult to explain and I have therefor added a GRAPH DATA of how I want it to be STEP TWO (STEP ONE see below) SHEET ONE - TASKLIST A B C ID STATUS STATUS CH Comment ------------------------------------------------------------- 1 Working 2006-11-01 New-Working; 2006-10-28-2006-11-01 2 Working 2006-10-29 3 Working 2006-11-01 SHEET TWO - GRAPH DATA A B C D Date New Working Closed Comment 2006-10-28 0 0 0 New: 1-0 Wanted: Still 1 2006-10-29 0 1 0 New: 1-0 Wanted: Still 1 2006-10-30 0 1 0 New: 1-0 Wanted: Still 1 2006-11-01 0 3 0 New: 1-0 Ok;Working: 2-3 OK 2006-11-02 #NA #NA #NA ToDay = 2006-11-01 The problem can be drilled down on how to "overwrite" the formula with the value of the result from the formula ---- I think :-) Can you try to help again? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
missing data points causes my line graph not to connect | Charts and Charting in Excel | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
How can I get 7.3 million data pts on one line graph? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |