Thread: wnt to look up fr a vlue in a clmn nd get the avg of the row
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
  
Posts: 9,221
Default wnt to look up fr a vlue in a clmn nd get the avg of the row
"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
---
Reply With QuoteReply With Quote