Graph over a time span

Discussion in 'Microsoft Excel Charting' started by daisy, Apr 14, 2010.

  1. daisy

    daisy Guest

    Hi, can anyone please help?

    Is there a way to graph from a span of time. We have Members with certain
    benefits that is good forever basically and then Members by their Contract St
    and End Dt and Members in special programs. So the data looks like for
    example:

    Member StDt EndDt ContractFees Contract Type
    Member A 1/1/2006 12/31/2009 $500 Program C
    Member B 1/1/2006 doesn't expire $2000 Program A
    Member C 6/1/2006 5/1/2010 $1000 Program B

    So I'd like to be able to count the # of members over time from Jan 2006,
    then June 2006 ... April 2010

    So January 2006 would show 2 members and June 2006 would show 3 members
    April 2010 would show 2 members etc...




    --
    Thank you in advance!!!!
     
    daisy, Apr 14, 2010
    #1
    1. Advertisements

  2. Hi Daisy,
    Your reference to 'graph' threw me but if I ignore that and read the rest of
    the question, it seems you want a table like this
    1-Jan-06 2
    1-Jul-06 3
    1-Jan-07 3
    .....
    I will assume your data is in A1;F100 with row 1 holding labels
    The major problem is the text "doesn't expire"
    I selected the C column and used Edit | Replace to blank these cells out
    (but see below)

    In H1 I entered 1-1-2006 (that is the first item in my table)
    In H2 I used the formula =DATE(YEAR(H1),MONTH(H1)+6,DAY(H1))
    I copied this down the column
    In I1 I entered the formula
    =SUMPRODUCT(($B$2:$B$100<=H1)*($C$2:$C$100>=H1))+SUMPRODUCT(($B$2:$B$100<=H1)*($B$2:$B$100>0)*($C$2:$C$100=0))
    and copied down the column
    The second term allows for the blanks in column C

    A better approach is to use Edit | Replace to convert "doesn't expire" to
    1/1/2099 (or some other date in the far distant future)
    Then the formula can be =SUMPRODUCT(($B$2:$B$100<=H1)*($C$2:$C$100>=H1))

    best wishes
    --
    Bernard Liengme
    Microsoft Excel MVP
    http://people.stfx.ca/bliengme
    "daisy" <> wrote in message
    news:...
    > Hi, can anyone please help?
    >
    > Is there a way to graph from a span of time. We have Members with certain
    > benefits that is good forever basically and then Members by their Contract
    > St
    > and End Dt and Members in special programs. So the data looks like for
    > example:
    >
    > Member StDt EndDt ContractFees Contract Type
    > Member A 1/1/2006 12/31/2009 $500 Program C
    > Member B 1/1/2006 doesn't expire $2000 Program A
    > Member C 6/1/2006 5/1/2010 $1000 Program B
    >
    > So I'd like to be able to count the # of members over time from Jan 2006,
    > then June 2006 ... April 2010
    >
    > So January 2006 would show 2 members and June 2006 would show 3 members
    > April 2010 would show 2 members etc...
    >
    >
    >
    >
    > --
    > Thank you in advance!!!!
     
    Bernard Liengme, Apr 14, 2010
    #2
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. LLC

    Half Bar Graph / Half Stack Graph ??

    LLC, Jul 8, 2004, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    1,162
  2. Guest
    Replies:
    1
    Views:
    2,162
    Tushar Mehta
    Jan 3, 2006
  3. Guest

    Line graph for cumulative number over time

    Guest, Apr 9, 2006, in forum: Microsoft Excel Charting
    Replies:
    0
    Views:
    526
    Guest
    Apr 9, 2006
  4. Guest

    How to graph 2 stacked colums/date, both over time?

    Guest, Sep 2, 2006, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    188
    Jon Peltier
    Sep 2, 2006
  5. JGeis

    Data over time graph

    JGeis, Sep 22, 2008, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    340
    JGeis
    Sep 23, 2008
Loading...

Share This Page