Thanks Biff. I'm with you on the Pivotable thing, but I think that may even
be the wrong application.

We need something that you can input (request/query) an existing worksheet,
and have information from that [searched] worksheet returned, and based on
pre-written formulae interpret and present the summary data in another
worksheet. It is basically (totally if you ask me) a database application
and request, however as I explained below, that is not an option we can
explore right now.

Do you think I might get any more replies to this thread, or do you think
some of the other experts might see that you have replied and might leave it
alone?

If so, and you think this one might be dead, do you have any advice on how I
could re-post or "bump" it?

Thanks again,
Greg


"T. Valko" wrote in message
...
Well crikey! "He" has to do some work!

Maybe a pivot table but I really can't help you with that 'cause I hate
pivot tables!

Biff

"Greg Purnell" wrote in message
...
Thanks for the reply Biff. The only thing is that he wants to be able to
type the symbol into a cell and hit "enter" or click a button or
something and have all of this data returned.

Also, I shortened the sample data by several columns, and just put 2
columns for examples' sake.

Any other ideas?

Thanks,

Greg


"T. Valko" wrote in message
...
Hmmm.....

Based on your sample data the average for ACG is -2.16%.

If each symbol is listed only once there is no need to use array
formulas.

With your sample data in the range A2:C9

G2 = ACG

This formula will get the average:

=AVERAGE(INDEX(B2:C9,MATCH(G2,A2:A9,0),))

You can use the same basic syntax for other calculations.

=STDEV(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MIN(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
=MAX(INDEX(B2:C9,MATCH(G2,A2:A9,0),))
etc

Biff

"Greg Purnell" wrote in message
...
My friend tried to stump me, but he didn't say I couldn't use you guys
to find an answer...so here it is:

He has this summary data on one worksheet (but with MUCH more data
columns/rows):

Symbol 9/14/2006 9/15/2006
ACG -2.22% -2.09%
ADF -9.79% -10.39%
ADRA -0.51% 0.03%
ADRD -0.41% 0.04%
ADRE -0.63% -0.03%
ADRU -0.56% 0.07%
ADX -14.39% -14.35%
AFB 7.38% 7.58%


and this is on the other worksheet:

ACG
Enter symbol here

-2.24%
Returns the average


obviously, the formula he has in the lower box (where result populated
is -2.24%) is: =AVERAGE(Data!3:3)

Very basic, I know. So I called him after he sent the email to find
out why he's making a mountain out of a molehill. Obviously he could
summarize the data on this worksheet simply by calculating the averages
on the source worksheet and just using the =Cell() formula to return
the results in a more readable format.

He does, however, (and as I suspected) want to bring in a lot more than
just averages (SDs, variances, etc., etc.). Basically, he wants to
type in a symbol and have it return a bunch of data. He has it figured
out one way with an array formula, but with over 1000 symbols, and
market data dating back to early 2006, it is obvious he has way too
much data. He did, of course, tell me that the array formulae did take
forever, so on to plan B.

I have some ideas, but I have other stuff I need to be working on, so I
just figured I'd come to the place where I've always been helped with
Excel (immensly!).

Basically, he just wants to be able to type in a symbol (a market
ticker symbol, but not for regular stocks, they deal with closed-end
funds) - and have it return some summary data. As I said before, he's
starting with "averages" but wants to apply the formula that I (well,
"we") come up with in order to return other types of data.

I did tell him right off the bat that this is an obvious and definite
application for Microsoft Access or SQL Server (or any DB app.), to
which he agreed. But his company is just a 2 man operation, and he
doesn't have the time to learn how to develop and implement one (a db),
nor the funds to have somebody else do so.

Also, if you have any comments, every time I bring up how he should be
doing this stuff on a database, he brings up "Filemaker Pro" - does
anyone professionally use that app.?

Thank you very much in advance all. You guys and gals have been
absolutely WONDERFUL with helping me with Excel over the years!! I
really appreciate it.

Best Regards:

Greg Purnell
jgpurnell13 - at - verizon.net