I had a bit of trouble understanding your message and current data structure.
----------------
My first suggestion would be to reorganise your data. It might be a bit of
work at first but you could then do some much more fancy stuff with it.
So if you had a single sheet with all your Data
Zoo Animal Quantity
London Elephant 3
London Giraffe 4
London Snake 5
NY Snake 2
NY Bear 1
Berlin Bear 4
Berlin Giraffe 6
you could then create a pivot table or a series of pivot tables like this
http://www.contextures.com/CreatePivotTable.html
------------------
Otherwise, I suggest you have a look at the help menu for SUMIF and COUNTIF.
In sheet 1 you could have
ColA ColB ColC
Row1 Animal Sum Count
Row2 Bear
Row3 Cow
The formula in B2 will be
=SUMIF(Sheet2!B:B,A2,Sheet2!C:C)
assuming on sheet 2 you have the animals in colB and the quantity thereof in
colC (you might have the zoo name in colA)
That will tell you the total number of each animal.
In C2 you will have
=COUNTIF(Sheet2!B:B,A2)
This will tell you the number of zoos that have the animal.
You can have another column to average them out.
--
Allllen
"Perplexed in Portland" wrote:
My example is a column of unique text locations each with an average of how
oftern used next to each on in the ajoining column. Lets say its a column of
100 various animals. on Sheet 1
Animals Types Running Average
Tigers 4.5
Lions 2.25
Bears 3.6
Monkeys 10.4
Penguins 12.1
etc to 100
And each Zoo is presented on Sheet 2 (see below) with its columnar list of
animals and how many they have on-site. But each zoo (there are many) lists
their animals on Sheet 2 never ordered the same way as Sheet 1 compelling me
to devise a way to LOOKUP the LOCATION on Sheet 1 first, then perform the
average in sheet 1 second column. Also sheet 2 list is never 100 animals long
but a subset of sheet 1 list of animals.
Animals Types Exact Count
Tigers 4
Lions 2
Bears 3
Monkeys 10
Penguins 12
etc
So what functions to I use to continuosly update the running average for
each animal on sheet 1?
Thank you in advance - this is driving me crazy.