A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Graph over a time span



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 05:15 AM posted to microsoft.public.excel.charting
daisy
external usenet poster
 
Posts: 94
Default Graph over a time span

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!!!!
  #2  
Old April 14th, 2010, 06:24 PM posted to microsoft.public.excel.charting
Bernard Liengme
external usenet poster
 
Posts: 516
Default Graph over a time span

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))+S UMPRODUCT(($B$2:$B$100=H1)*($B$2:$B$1000)*($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
...
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!!!!


 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.