| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Way to average a group of results from using LOOKUP?
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Way to average a group of results from using LOOKUP?
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 -- Regards, Dave "L.Chua" wrote: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Way to average a group of results from using LOOKUP?
Thanks guru!
"David Billigmeier" wrote: 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 -- Regards, Dave "L.Chua" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for variable-height group average | Excel Worksheet Functions | |||
| Average a group, where grouping is Conditional on other col.??? | Excel Discussion (Misc queries) | |||
| sum the results of a group of formulas | Excel Worksheet Functions | |||
| How do I extract 3 middle results and average them in excel | Excel Worksheet Functions | |||
| IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
Linear Mode
