This is not what I was looking for.
I want to calculate the average of scores achieved by a guy.

for e.g:
A B C D E
1 2 3 4
smith 98 65 98 96
donald 97 94 92 93
alex 91 90 89 97
ronald 91 94 95 97
jose 94 95 92 90
brad 94 91 95 94
donald 98 65 98 96
brad 97 94 92 93
jose 91 90 89 97
alex 91 94 95 97
davis 94 95 92 90
smith 94 91 95 94

average of Scores achieved by alex(if his name is there in column A)

What formulae should I derive to calculate this?


"Max" wrote:

"Jeffrey" wrote:
I want to look up for a value in a column
and get the average of the row in front of that particular cell.

One venture ..

Assuming source table in Sheet1,
with lookup values listed in A3 down,
and "corresponding" numeric values in B2 across/down

In another sheet,
with the same lookup values listed in A3 down

Put in B1:
=AVERAGE(OFFSET(Sheet1!$1:$1,MATCH(A3,Sheet1!A:A,0 )-2,))
B1 will return the average for the row above the corresponding lookup value
in Sheet1 (this is what you wanted, right?). Copy B1 down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---