How Do I? Perform simplae Average on a indexed text location

[Perplexed in Portland]

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.

 

[Allllen]

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.

 

[Perplexed in Portland]

Allllen......
1st - thank you for the reply
2nd - I will have to understand your answers so I will dig into the SUMIF an
COUNTIF functions and what thye do.
3rd - I have no way to change to the list of "animals" in my case since they
are fixed. But as I capture each zoo's own unique list which is only 10 items
total - they 1st have to be found in the master list then their number
averaged with all the rest.

I have my Excel manuals out looking at hoiw your answer will be applied.
Thanks again....

 

[Perplexed in Portland]

Allllen.....
I tried your suggestions but they assume values are being pivoted or
operation is being performed resulting in SUMs or others things. In my case
my information is Indexed to a master list and the indvidual records I need
to extract the information are not ordered like the master list. I tried a
LOCATE and it returned a 1 (True) for the correct entry. Then I thought to do
a IF(Locate) is True perfrom an Average. But the LOCATE does not return the
LOCATION reference found.

Ex. I have 200 different Zoo records, and the Function must "find" the right
animal in the Master list before Averaging the previous number with the
current one. LOCATE only finds the right animal then I need to add one column
to its location to get to the running average column.

Hope this helps explain my data set a bit more. I tried re-orging the data
set but there is no way to create a pivot table. If it was then it would be
straight forward.

Still Perplexed in Portland

 

[Max]

Here's another view & venture on your original post ...

Source data is assumed running in Sheet2's A2:B2 down,
eg:

Tigers 4
Lions 2
Bears 3
Lions 7
Bears 5
Tigers 9
etc

In Sheet1,
With the unique "animals", eg: Tigers, etc listed in A2 down
Paste this into B2, then press CTRL+SHIFT+ENTER to confirm the formula:
=AVERAGE(IF(Sheet2!A$2:A$100=A2,Sheet2!B$2:B$100))
Copy B2 down. Adapt the ranges to suit the actual extents of your data.

Success? High-five it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

 

[Perplexed in Portland]

"Max"......
Actually.....I though you were on to something and it took me a whole day to
figure out that that metod returns a #Value since your Average is missing the
second term(number). So I messed around with it more and tried doing a
IF(Average), then a IF(EXACT) then Average. Finally got it so only the first
term would average based on the first indexed location. But it would not
"walk" down the rows to the next location......hmmmm

I really dont understand relative and absolute references - you know $
before or $ after.

Still thinking about what I though would be a simple problem......but is
twisting my brain.....
....PIP

 

[Max]

.. returns a #Value since your Average is missing the second term(number)
No, it should work as advertised. If you get #value! errors, that means
you've got these errors somewhere in Sheet2's col B, which is supposed to
house only numbers. Do a check on your data, clear it up.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Perplexed in Portland]

MAX....
I have created the Sheets 1 and 2 for your reference and you can see the
result.
Here is the REsult of Sheet 1 located at A2:B2 beginning with tigers and 4.22

Animals Average
Tigers 4.22
Lions 4.22
Monkeys 4.22
Horses 4.22
Seals 0.00
Elephants 0.00
Parrots 0.00
Sloths 0.00
Bears 0.00

Here is the data set on Sheet 2 also beginning at A2:B2 with Tigers and 2

Tigers 2
Lions 2
Monkeys 10
Horses 3
Tigers 4
Seals 2
Lions 6
Monkeys 6
Elephants 3

Here is your formula:
=AVERAGE(IF(Sheet2!A$2:A$10=A2,Sheet2!B$2:B$10))

The probem is the range. Tigers should result in 3, not 4.22. In fact the
Average is being taken for the entire Range in B2:B10 when the challenge was
to Average only Tigers with tigers, etc....

So I appreciate your help and I admit I am a little disappointed I have not
been able to figure this out without resorting to VBA code...

Thanks
Mayne

 

[Max]

Believe you didn't "press CTRL+SHIFT+ENTER (CSE) to confirm the formula" in
B2 as advised in my 1st response. That special way of confirming the formula
(array-enter), instead of just pressing ENTER, is necessary for it to work
properly. If you did the CSE correctly, you should see curly braces: { }
wrapped around the formula by Excel in the formula bar. Sight these curly
braces as a visual check that it's correctly array-entered before you copy
down B2. If you don't see any braces, then it isn't array-entered, and the
formula will NOT return correct results. Just click inside the formula bar,
do the CSE confirmation again. Re-sight for the curlies in the formula bar.

Based on your sample data, these should be the results appearing when you
array enter correctly in B2, and then copy B2 down

Animals Average
Tigers 3
Lions 4
Monkeys 8
Horses 3
Seals 2
Elephants 3
Parrots #DIV/0!
Sloths #DIV/0!
Bears #DIV/0!

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Perplexed in Portland]

AH So Excel Master.....
when I did the CTRL+SHIFT+ENTER - nothing happened meaning I did not enter
an Array of values....for one reason or another.
Good. I am off and running and will try it.
Many thanks

-Perfect in Portland