How do you automatically chart last 30 days of data

Tim Nolan

unread,

Jun 27, 2001, 12:03:00 PM6/27/01

to

I've got a line chart plotting our system uptime over time.
What I'd like to do is chart the last 30 days of uptime automatically. Can
anyone provide some input on what how I'd do that?

Thanks

Debra Dalgleish

unread,

Jun 27, 2001, 12:27:14 PM6/27/01

to

You could set up a second worksheet with the same headings as the one
that contains all the data. For example:
Date Value 1 Value 2 Value 3 Value 4 Value 5

Leave a blank column and add another column named "Age"
In the age column, type 29, 28, 27, etc. down to zero.

In cell A2, for the first date value, type the formula :
=today()-H2
where H is the Age column.

You can name the range of cells which contain the uptime data on your
main worksheet (e.g. UptimeData).

In cell B2, use a VLOOKUP formula to pull the data from the main
worksheet:
=VLOOKUP($A2,UptimeData,2)

In cell C2:
=VLOOKUP($A2,UptimeData,3)

etc., for all your columns.

Fill down with these formulas to today's date.

Base your chart on this range.

S...@internet.org

unread,

Jun 27, 2001, 12:34:46 PM6/27/01

to

Read the thread: Automatically Update Charts
from June 14, 2001

Also, keep these other sites handy:
Stephen Bullen's site
http://www.bmsltd.co.uk/Excel/Default.htm

Tushar Mehta's site
http://www.tushar-mehta.com/
(see the Dynamic Charts page)

-- Sam

"Tim Nolan" <tno...@netpcs.com> wrote in message
news:Usn_6.979$ef.1...@newsread2.prod.itd.earthlink.net...

David Lee

unread,

Jun 27, 2001, 1:29:49 PM6/27/01

to

It all depends upon how you are arranging your data.

Are you are maintaining a single column of data, each cell containing the
uptime for that day, and wish to plot the 30 most recent values?

If so it will depend upon whether you add the latest number to the bottom
of the column or insert it at the top. Either way define a name
(insert->name->define) and refer it to a formula that returns a range
containing the 30 most recent values. Use this name for the values in your
line chart.

eg assuming that your data is in column A starting in A1 then define:

first30=OFFSET(Sheet1!$A$1,0,0,30) if you're adding data to the top of
the column

or:

last30=OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A)-30,0,30) if the new data is
added at the bottom.

In the latter case you must make sure that there is no other numerical data
in column A otherwise this will cause COUNT to return the wrong value and
offset your plot.

Tim Nolan <tno...@netpcs.com> wrote in article
<Usn_6.979$ef.1...@newsread2.prod.itd.earthlink.net>...

TBartkus

unread,

Jun 28, 2001, 1:20:34 PM6/28/01

to

Dates in the first column, Data in the adjacent column to the right

Sort this table (both columns) with dates DESCENDING.

Attach the chart to the top 30 data pairs (top 30 days!)

Voila!

From now on:
Add each new days data to the end of the table - then sort the entire
table descending.

If you want to get fancy - (and you don't need to!)
Make a simple macro that gets the new days record from the operator via
Data_Form then trigger the descending sort automatically.

Your chart always displays the last 30 days.
Thomas Bartkus

"Tim Nolan" <tno...@netpcs.com> wrote in message
news:Usn_6.979$ef.1...@newsread2.prod.itd.earthlink.net...