Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Averaging numbers but ignoring < and - entries
I have a column of data as illustrated below than contains numbers, blank
cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you |
#2
|
|||
|
|||
On Wed, 23 Feb 2005 06:29:03 -0800, KIM wrote:
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you If your entries are in, for example, A1:A8, the formula =AVERAGE(A1:A8) --ron |
#3
|
|||
|
|||
How about if there is a ' infront of every entry and - entered into some
cells? I need the ' to trick access into believing that all the entries are TEXT rather than a mixture of TEXT and NUMBERS. e.g. '0.3 '<0.001 '0.01 '<0.01 '- '0.1 Should give an answer of 0.0842 Thank you "Ron Rosenfeld" wrote: On Wed, 23 Feb 2005 06:29:03 -0800, KIM wrote: I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.0842) 0.3 <0.001 0.01 <0.01 0.1 Thank you If your entries are in, for example, A1:A8, the formula =AVERAGE(A1:A8) --ron |
#4
|
|||
|
|||
Take a look at the VALUE function |:)
"KIM" wrote: How about if there is a ' infront of every entry and - entered into some cells? I need the ' to trick access into believing that all the entries are TEXT rather than a mixture of TEXT and NUMBERS. e.g. '0.3 '<0.001 '0.01 '<0.01 '- '0.1 Should give an answer of 0.0842 Thank you "Ron Rosenfeld" wrote: On Wed, 23 Feb 2005 06:29:03 -0800, KIM wrote: I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.0842) 0.3 <0.001 0.01 <0.01 0.1 Thank you If your entries are in, for example, A1:A8, the formula =AVERAGE(A1:A8) --ron |
#5
|
|||
|
|||
On Wed, 23 Feb 2005 07:45:03 -0800, KIM wrote:
How about if there is a ' infront of every entry and - entered into some cells? I need the ' to trick access into believing that all the entries are TEXT rather than a mixture of TEXT and NUMBERS. e.g. '0.3 '<0.001 '0.01 '<0.01 '- '0.1 Should give an answer of 0.0842 Thank you I don't know. I don't understand how you are getting an answer of 0.0842 with those values. If an answer of 0.136667 might be correct, then the *array* formula: =AVERAGE(IF(ISNUMBER(-SUBSTITUTE(A1:A9,"'","")),--SUBSTITUTE(A1:A9,"'",""))) will strip off leading single quotes and average what is a number. To enter an *array* formula, after typing or pasting in the formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Averaging the last 5 entries in a row | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
multiple entries | Excel Worksheet Functions | |||
Add numbers for duplicate entries then delete | Excel Worksheet Functions |