PDA

View Full Version : X-Y scatter graph refering a series of data log



Ann_BBO
04-23-2009, 01:45 AM
Hi All,

I have one marco to input the log data from text file into workbook.
Each log has a Date to indicate it. Now, I have over 1 year data log and would like to extract a 4 data log into X-Y scatter graph. The 4 data log which are “Most Recent”, “One-Month Before”, “Half-a-Year Before” and “Earliest Reference” curves in the graph referring to Date's data log.

Suppose Range (“B1”) = 4/21/2009
4/21/2009 4/1/2009 3/19/2009 10/12/2008 4/1/2008
1 -45.62 -58.89 -68.83 -65.61 -60.61
2 -47.11 -54.90 -56.90 -63.52 -62.52
3 -50.55 -57.31 -57.31 -68.83 -68.83




Most Recent = 4/21/2009 data log
One Month Before = 3/19/2009 data log
Half-a-Year Before = 10/12/2008 data log
Earliest Reference = 4/1/2008 data log.

X=axis = 1,2,3

I don't know how to compile it. I have try the Month function in excel but the problem is data log is very large.

The stimulated attachment is enclosed. Hope that all can help.

Thanks
Ann

mdmackillop
04-23-2009, 11:19 AM
Is it this chart you are after? If so, what do you wish to automate? Are you looking to extract these fields from your larger data source?

Ann_BBO
04-23-2009, 05:48 PM
Yes. I would like to automate to plot the chart from a series of data by VBA. Actually, I have over 1 year weekly data.
e.g. 4/21/2009 4/14/2009 4/07/2009 03/31/2009 … 3/17/2009 … 10/12/2008. … 4/1/2008

However, I would like to have a 4 data logs display in the X-Y scatter graph. The definitions of 4 data logs are:

Most Recent = 4/21/2009 data log (i.e. the latest data log)
One Month Before = 3/17/2009 data log (i.e. Comparing with the latest data log. The difference is over 1 month.)
Half-a-Year Before = 10/12/2008 data log (i.e. Comparing with the latest data log, The difference is over 6 month.)
Earliest Reference = 4/1/2008 data log. (i.e. The first data log)

I know the strategy is comparing with the date but I am not sure how to compile in marco.

Thanks,
Ann

mdmackillop
04-24-2009, 12:39 AM
Can you post sample data and layout

Ann_BBO
04-24-2009, 08:30 AM
Hi mdmackillop,

Here is the new sample file. Please see it.

BTW, Thanks for your support

Thanks,
Ann

mdmackillop
04-24-2009, 11:58 AM
This uses formulae to extract relevant data to a range on which the chart is based

Ann_BBO
04-27-2009, 05:45 PM
Hi md,

Thanks. For "One month" and "Six month", I find that you use the "Most recent date – 28 and – 182 " to get the exactly date respectively. According the exactly date, you use the lookup function to find the corresponding date and then data.

However, as I said before, I will update the latest date’s data into database weekly or daily. Therefore, “-28” & “-182” exactly value cannot approach. So How to get the most nearest date (one month & six month before) according the database. (by marco or lookup function?)

Thanks,
Ann.

mdmackillop
04-28-2009, 02:50 AM
This uses Match to find the nearest value. I added some conditional formatting to highlight the selected data