HLookup ???
 

I got asked to help analyze the employee survey results.

The spreadsheet is set up with like :
These are the question categories:
Dept Yrs w/Comp Q D Q R
A A 1 3 4 5
B A 1 3 5 6
etc.


I can't come up a formula to calculate the averages by category (i.e.
Q,D,R)

I tried simple arrays like =SUM((D2:AVQ2="G")*D3:AV25), but that errors out.

I tried HLOOKUP, but that only returns the value from one row.

Any suggestions?

Hi
try
=AVERAGE(OFFSET(A2:A100,0,MATCH("Q",A2:X2,0)-1))

--
Regards
Frank Kabel
Frankfurt, Germany

scott wrote:
I got asked to help analyze the employee survey results.

The spreadsheet is set up with like :
These are the question categories:
Dept Yrs w/Comp Q D Q R
A A 1 3 4 5
B A 1 3 5 6
etc.


I can't come up a formula to calculate the averages by category (i.e.
Q,D,R)

I tried simple arrays like =SUM((D2:AVQ2="G")*D3:AV25), but that
errors out.
I tried HLOOKUP, but that only returns the value from one row.

Any suggestions?

Thank you. Works great.




Frank Kabel wrote:

Hi
try
=AVERAGE(OFFSET(A2:A100,0,MATCH("Q",A2:X2,0)-1))