PC Review


Reply
Thread Tools Rate Thread

Graph over a time span

 
 
daisy
Guest
Posts: n/a
 
      14th Apr 2010
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!!!!
 
Reply With Quote
 
 
 
 
Bernard Liengme
Guest
Posts: n/a
 
      14th Apr 2010
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!!!!


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bar graph and stacked bar graph on same graph BRB Microsoft Excel Misc 3 22nd Aug 2008 07:26 PM
Re: Holidays span two days when they should span one Diane Poremsky {MVP} Microsoft Outlook 7 28th Apr 2008 10:50 PM
Hyperlinkage of one graph with another graph or Drill down graph =?Utf-8?B?U2FuamF5IEt1bWFyIFNpbmdo?= Microsoft Excel Charting 1 3rd Jan 2006 01:22 PM
<span></span> =?Utf-8?B?RGVicmE=?= Microsoft Frontpage 9 1st Dec 2004 11:19 PM
Outlook 2002 crashes over and over and over and over and over and over typoo Microsoft Outlook Discussion 5 7th Mar 2004 08:12 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:43 PM.