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" wrote:

.. 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" wrote:
"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