Seemingly elegant solutions for excel intermediate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

edward.montague@gmail.com - 06 Feb 2007 10:32 GMT

Hi. I have a list of data that is about 350 rows dwon by about 100
across, and need elegant ways to present this data. The data is of a
list of stocks from about 10 countries and from about 10 sub-sectors.

If using the data in one worksheet and using dropdown filters, is
there formula that can average/sum etc only data that is presented on
the, and which ignores the hidden lines?

If I would like to replicate the data from this front sheet in
seperate worksheets divided up by country and subsector, what would
you recommmed to do this?

Many thanks for any help - much appreciated!

Reply to this Message

Roger Govier - 06 Feb 2007 11:02 GMT

Hi Edward

I would take a look at using Pivot Tables, dragging some of your
variables to the Page area.

Take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx

Signature

Regards

Roger Govier

> Hi. I have a list of data that is about 350 rows dwon by about 100
> across, and need elegant ways to present this data. The data is of a
[quoted text clipped - 9 lines]
>
> Many thanks for any help - much appreciated!

Reply to this Message

edward.montague@gmail.com - 06 Feb 2007 11:11 GMT

> Hi Edward
>
[quoted text clipped - 31 lines]
>
> - Show quoted text -

Thanks Roger for you help - I'll give it a whirl now.

Reply to this Message

KC Rippstein - 06 Feb 2007 14:00 GMT

Pivot tables is certainly a great way to go, but if for some reason you
don't have time to learn them, the function you are looking for is called
SUBTOTAL. Excel's help gives you a list of the available functions in
SUBTOTAL, which can do sum, average, min, max, count, etc. of just the
visible rows.
If you're using Excel 2003, you can just highlight your entire data table
and go to Data -> List -> Create List. If you set it up this way, then just
go back to Data -> List -> Toggle Total Row and you will see that they have
already built in that SUBTOTAL function for you into the totals row and you
simply select the word for what you want the total to be (Average, Count,
Sum) instead of trying to remember the formula's syntax.
- KC

>> Hi Edward
>>
[quoted text clipped - 34 lines]
>
> Thanks Roger for you help - I'll give it a whirl now.

Reply to this Message