Use array for lookup value, to return array of lookups
 

I have a row of values that I want to average, but the cells that need to be
averaged contain text that has to be matched to a numerical value for the
purpose of averaging.

eg "Fair","Poor","Excellent","Poor" etc etc as values in the row

where
Excellent = 100
Good = 85
Fair = 75
Poor = 50

So for a row where the text data is in C2:T2 and the lookup table with the
corresponding numerical values is called range "Numerical_LU", I'm trying to
use the following formula in C1:
{=average(vlookup(C2:T2,Numerical_LU,2,0))}

But all I can get is the value of the lookup for the first cell (C2). Is
there are way to get vlookup to evaluate the whole array of C2:T2 and return
the results as an array?

Cheers,

Glen

Use array for lookup value, to return array of lookups
 

Try this:

=SUMPRODUCT(SUMIF(INDEX(Numerical_LU,,1),C2:T2,IND EX(Numerical_LU,,2)))/COUNTA(C2:T2)

--
Biff
Microsoft Excel MVP


"Glen" wrote in message
...
I have a row of values that I want to average, but the cells that need to
be
averaged contain text that has to be matched to a numerical value for the
purpose of averaging.

eg "Fair","Poor","Excellent","Poor" etc etc as values in the row

where
Excellent = 100
Good = 85
Fair = 75
Poor = 50

So for a row where the text data is in C2:T2 and the lookup table with the
corresponding numerical values is called range "Numerical_LU", I'm trying
to
use the following formula in C1:
{=average(vlookup(C2:T2,Numerical_LU,2,0))}

But all I can get is the value of the lookup for the first cell (C2). Is
there are way to get vlookup to evaluate the whole array of C2:T2 and
return
the results as an array?

Cheers,

Glen

Use array for lookup value, to return array of lookups
 

Works a treat.

Cheers, Glen

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUMIF(INDEX(Numerical_LU,,1),C2:T2,IND EX(Numerical_LU,,2)))/COUNTA(C2:T2)

--
Biff
Microsoft Excel MVP


"Glen" wrote in message
...
I have a row of values that I want to average, but the cells that need to
be
averaged contain text that has to be matched to a numerical value for the
purpose of averaging.

eg "Fair","Poor","Excellent","Poor" etc etc as values in the row

where
Excellent = 100
Good = 85
Fair = 75
Poor = 50

So for a row where the text data is in C2:T2 and the lookup table with the
corresponding numerical values is called range "Numerical_LU", I'm trying
to
use the following formula in C1:
{=average(vlookup(C2:T2,Numerical_LU,2,0))}

But all I can get is the value of the lookup for the first cell (C2). Is
there are way to get vlookup to evaluate the whole array of C2:T2 and
return
the results as an array?

Cheers,

Glen

Use array for lookup value, to return array of lookups
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Glen" wrote in message
...
Works a treat.

Cheers, Glen

"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUMIF(INDEX(Numerical_LU,,1),C2:T2,IND EX(Numerical_LU,,2)))/COUNTA(C2:T2)

--
Biff
Microsoft Excel MVP


"Glen" wrote in message
...
I have a row of values that I want to average, but the cells that need
to
be
averaged contain text that has to be matched to a numerical value for
the
purpose of averaging.

eg "Fair","Poor","Excellent","Poor" etc etc as values in the row

where
Excellent = 100
Good = 85
Fair = 75
Poor = 50

So for a row where the text data is in C2:T2 and the lookup table with
the
corresponding numerical values is called range "Numerical_LU", I'm
trying
to
use the following formula in C1:
{=average(vlookup(C2:T2,Numerical_LU,2,0))}

But all I can get is the value of the lookup for the first cell (C2).
Is
there are way to get vlookup to evaluate the whole array of C2:T2 and
return
the results as an array?

Cheers,

Glen