Way to average a group of results from using LOOKUP?

[L.Chua]

I have a table of scores achieved as a result of using the LOOKUP function
(typical % converted to score type of scenario). I am trying to average (say
10) resulting scores but am running into problems where I constantly receive
the #div/0 error or receive a 0. I can't see where I'm dividing by 0.

Any thoughts on how to work around this?

Thanks!

 

[David Billigmeier]

It's because the values returned by the lookup function are formatted as
text. To fix, you can do a couple things:

Add a double negative (--) to the beginning of each lookup function, this
will force Excel to return the results formatted as a number (Note: Simply
doing a <Format><Cells><Number> will NOT work)

=--LOOKUP(...)

You can also chage it at the source by forcing Excel to treat your lookup
range numerically. To do this:

Copy a blank cell
Select your range
<Edit><Paste Special...>
Select "Values" and "Add"
Click OK

 

[L.Chua]

Thanks guru!

It's because the values returned by the lookup function are formatted as
text. To fix, you can do a couple things:

Add a double negative (--) to the beginning of each lookup function, this
will force Excel to return the results formatted as a number (Note: Simply
doing a <Format><Cells><Number> will NOT work)

=--LOOKUP(...)

You can also chage it at the source by forcing Excel to treat your lookup
range numerically. To do this:

Copy a blank cell
Select your range
<Edit><Paste Special...>
Select "Values" and "Add"
Click OK