Graphing Filtered Data +

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

There is a spreadsheet with historical dates in column "A" (ex. 1999,
2000, 2001, etc.). These dates are repeated for about 10 to 15 times
each, depending on the number of data points in that year.


Ex.

1999 $750.00
1999 $1240.00
1999 $1484.00
1999 $345.00
2000 $675.00
2000 $397.00


I am trying to do two things with my data.

1. Graph the data on a line or bar chart (the data is FILTERED).


2. Sum the costs for each individual year and then graph the summed
cost in relation to the year. (remember, the data is FILTERED). The
data that is displayed at any given time should be summed and graphed.



I hope someone can do this.

Thanks a lot.
 
Is there a function or command button that will allow me to transfer the
data on the filtered page (at a given moment in time) to a new sheet??
Then, from that sheet I can run a SUMIF function to get the sum of the
costs based on the respective year.


Does anyone know the visual basic code, or a function to complete
this??
 
There is a spreadsheet with historical dates in column "A" (ex. 1999,
2000, 2001, etc.). These dates are repeated for about 10 to 15 times
each, depending on the number of data points in that year.

Ex.

1999 $750.00
1999 $1240.00
1999 $1484.00
1999 $345.00
2000 $675.00
2000 $397.00


I am trying to do two things with my data.

1. Graph the data on a line or bar chart (the data is FILTERED).
Click to expand...

You should be able to chart it as per normal procedure. One option you
can check it Tools | Options | Chart tab | Plot only visible cells.
2. Sum the costs for each individual year and then graph the summed
cost in relation to the year. (remember, the data is FILTERED). The
data that is displayed at any given time should be summed and graphed.
Click to expand...

Use the SUBTOTAL function. I think this also answers your followup
question.
 
Select a cell in the filtered list, then press Ctrl-* (the asterix in
the number keypad) to select the contiguous area. If you copy this
selection and paste it into a new sheet, only the visible rows will be
copied.

But I think it's more useful to follow Dave's suggestion, and make a
pivot table from the unfiltered list. Work out the filters from the
pivot fields you choose to show. Then have the pivot table run the sums
for you.

- Jon
 
Thanks for the help.

I have tried to create a pivot table, which I am new at doing, but the
data seemed way to large. I have about 20 columns and 650 rows.

If you have any other ideas or better ways of doing this, I would love
to see them. Otherwise, I will use "ctrl-*" and copy the data
 
Select a cell in the filtered list, then press Ctrl-* (the asterix in
the number keypad) to select the contiguous area. If you copy this
selection and paste it into a new sheet, only the visible rows will be
copied.

But I think it's more useful to follow Dave's suggestion, and make a
pivot table from the unfiltered list. Work out the filters from the
pivot fields you choose to show. Then have the pivot table run the sums
for you.

- Jon
Click to expand...

Jon gives me credit for the pivot table idea, but I think it was his
idea. And a good one at that.

Todd, can you post a sample of your data? I suspect you can get your
charts by using subtotals and filtering judiciously.

For example, let's say you sort your data by year. Then add subtotals
(Data | Add subtotals) for each year. Now you filter that data as you did
before. If you chart the subtotals, only the filtered subtotals will show
up.
 
For whatever reason, I was asked before not to post attachments on this
site. It must bog down the server. I think I will go with the sumif
function though. I think I have way too many columns to be creating a
pivot table.

I will just creat a SUMIF function on another sheet to total each
year's costs. Then from there I will graph based on visible cells.
 
The reasons include the size of the attachment and use of bandwidth, and
the fact that lots of attachments are not benign samples of a
troublesome worksheet. I don't think anyone would gripe, however, if
you included a dozen or two lines pasted as text from your worksheet.
Enough to give us a clue what you're doing.

- Jon
 
hduh
Jon Peltier said:
Select a cell in the filtered list, then press Ctrl-* (the asterix in
the number keypad) to select the contiguous area. If you copy this
selection and paste it into a new sheet, only the visible rows will be
copied.

But I think it's more useful to follow Dave's suggestion, and make a
pivot table from the unfiltered list. Work out the filters from the
pivot fields you choose to show. Then have the pivot table run the sums
for you.

- Jon
Click to expand...
 
Back
Top