Avearging columns with less than (<) text entries

Question 1. How do I average a column that contains a mixture of numbers and
less than entries?
e.g. 12
<1
10
<5

Question 2. Is it possible to include the less than values in the average
calculation (i.e so excel ignores the less than sign and calcualted the
avearge using the number that is next to the less than sign)?

Question 3. Is it possible to include some less than values (e.g. <10) in
the average calculation but ignore say other less than values (e.g. <100)?

Thank you!

One way

=SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(A2:A200,"<",""),">","")))

will work for greater than as well, if you know you only have less than it
can be simplified to

=SUMPRODUCT(--(0&SUBSTITUTE(A2:A200,"<","")))

note that is you have other text entries it will return a value error

--

Regards,

Peo Sjoblom



"KIM" <[email protected]> wrote in message
news:[email protected]...
> Question 1. How do I average a column that contains a mixture of numbers
and
> less than entries?
> e.g. 12
> <1
> 10
> <5
>
> Question 2. Is it possible to include the less than values in the average
> calculation (i.e so excel ignores the less than sign and calcualted the
> avearge using the number that is next to the less than sign)?
>
> Question 3. Is it possible to include some less than values (e.g. <10) in
> the average calculation but ignore say other less than values (e.g. <100)?
>
> Thank you!

KIM,

Answer 1:

=AVERAGE(A1:A10)

But this ignores the cells with the < signed values. For your example, this
will return 11.

Answer 2:

Array enter (enter using Ctrl-Shift-Enter) the formula

=AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))

where A1:A10 are the cells with the values. Note, all the cells musxt be
filled - otherwise, they will be treated as 0. For your example, this
formula will return 7.

Answer 3:

Array enter (enter using Ctrl-Shift-Enter) the formula

=AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
2,15)),""),A1:A5))

Though this will also ignore <1000, <500, etc. as long as the resulting
number (to the right of the < sign) is greater than or equal to 100.


HTH,
Bernie
MS Excel MVP

"KIM" <[email protected]> wrote in message
news:[email protected]...
> Question 1. How do I average a column that contains a mixture of numbers
and
> less than entries?
> e.g. 12
> <1
> 10
> <5
>
> Question 2. Is it possible to include the less than values in the average
> calculation (i.e so excel ignores the less than sign and calcualted the
> avearge using the number that is next to the less than sign)?
>
> Question 3. Is it possible to include some less than values (e.g. <10) in
> the average calculation but ignore say other less than values (e.g. <100)?
>
> Thank you!

Sorry replace sumproduct with average and enter with ctrl + shift & enter

--

Regards,

Peo Sjoblom


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> One way
>
> =SUMPRODUCT(--(0&SUBSTITUTE(SUBSTITUTE(A2:A200,"<",""),">","")))
>
> will work for greater than as well, if you know you only have less than it
> can be simplified to
>
> =SUMPRODUCT(--(0&SUBSTITUTE(A2:A200,"<","")))
>
> note that is you have other text entries it will return a value error
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
> "KIM" <[email protected]> wrote in message
> news:[email protected]...
> > Question 1. How do I average a column that contains a mixture of numbers
> and
> > less than entries?
> > e.g. 12
> > <1
> > 10
> > <5
> >
> > Question 2. Is it possible to include the less than values in the
average
> > calculation (i.e so excel ignores the less than sign and calcualted the
> > avearge using the number that is next to the less than sign)?
> >
> > Question 3. Is it possible to include some less than values (e.g. <10)
in
> > the average calculation but ignore say other less than values (e.g.
<100)?
> >
> > Thank you!
>
>

Thank you for the reply - maybe I'm entering the formula incorrectly because
it returns a VAULE! error. Can you confirm what you mean by Array entering
(enter using Ctrl-Shift-Enter) the formula? I have just typed it straight
in. And what are the 2 and 15 values for?

"Bernie Deitrick" wrote:

> KIM,
>
> Answer 1:
>
> =AVERAGE(A1:A10)
>
> But this ignores the cells with the < signed values. For your example, this
> will return 11.
>
> Answer 2:
>
> Array enter (enter using Ctrl-Shift-Enter) the formula
>
> =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
>
> where A1:A10 are the cells with the values. Note, all the cells musxt be
> filled - otherwise, they will be treated as 0. For your example, this
> formula will return 7.
>
> Answer 3:
>
> Array enter (enter using Ctrl-Shift-Enter) the formula
>
> =AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> 2,15)),""),A1:A5))
>
> Though this will also ignore <1000, <500, etc. as long as the resulting
> number (to the right of the < sign) is greater than or equal to 100.
>
>
> HTH,
> Bernie
> MS Excel MVP
>
> "KIM" <[email protected]> wrote in message
> news:[email protected]...
> > Question 1. How do I average a column that contains a mixture of numbers
> and
> > less than entries?
> > e.g. 12
> > <1
> > 10
> > <5
> >
> > Question 2. Is it possible to include the less than values in the average
> > calculation (i.e so excel ignores the less than sign and calcualted the
> > avearge using the number that is next to the less than sign)?
> >
> > Question 3. Is it possible to include some less than values (e.g. <10) in
> > the average calculation but ignore say other less than values (e.g. <100)?
> >
> > Thank you!
>
>
>

Sorry guys just tried both formulas again but finished by pressing ctrl +
shift & enter and it has worked. Sorry novice about - what does ctrl + shift
& enter do?

Thank you

Kim,

Array entering is a special case of entering formulas, which forces Excel to
evaluate each of the cells on a step by step basis. You type in the
formula, press Ctrl, then Shift, and with both of those still held down,
press Enter.

The 2 is passed to the MID function, which means it extracts the value from
the cell starting at the second position, or just to the right of the <
sign. The 15 is the length of the longest number that Excel can handle, so
I just used that as a guess for how long your number might be.

You may be getting the Value# error if there are other spaces or
non-printing characters in the cell, to the left of the < sign. For one of
your cells with the < character, use a formula like
=LEN(A1)
to figure out how long the string is. If your string is <2 and that formula
returns a number greater than 2, then try increasing the 2 parameter of the
MID function.

HTH,
Bernie
MS Excel MVP

"KIM" <[email protected]> wrote in message
news:[email protected]...
> Thank you for the reply - maybe I'm entering the formula incorrectly
because
> it returns a VAULE! error. Can you confirm what you mean by Array
entering
> (enter using Ctrl-Shift-Enter) the formula? I have just typed it straight
> in. And what are the 2 and 15 values for?
>
> "Bernie Deitrick" wrote:
>
> > KIM,
> >
> > Answer 1:
> >
> > =AVERAGE(A1:A10)
> >
> > But this ignores the cells with the < signed values. For your example,
this
> > will return 11.
> >
> > Answer 2:
> >
> > Array enter (enter using Ctrl-Shift-Enter) the formula
> >
> > =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
> >
> > where A1:A10 are the cells with the values. Note, all the cells musxt be
> > filled - otherwise, they will be treated as 0. For your example, this
> > formula will return 7.
> >
> > Answer 3:
> >
> > Array enter (enter using Ctrl-Shift-Enter) the formula
> >
> >
=AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> > 2,15)),""),A1:A5))
> >
> > Though this will also ignore <1000, <500, etc. as long as the resulting
> > number (to the right of the < sign) is greater than or equal to 100.
> >
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "KIM" <[email protected]> wrote in message
> > news:[email protected]...
> > > Question 1. How do I average a column that contains a mixture of
numbers
> > and
> > > less than entries?
> > > e.g. 12
> > > <1
> > > 10
> > > <5
> > >
> > > Question 2. Is it possible to include the less than values in the
average
> > > calculation (i.e so excel ignores the less than sign and calcualted
the
> > > avearge using the number that is next to the less than sign)?
> > >
> > > Question 3. Is it possible to include some less than values (e.g. <10)
in
> > > the average calculation but ignore say other less than values (e.g.
<100)?
> > >
> > > Thank you!
> >
> >
> >

It's because it's an array formula, instead of one cell like left(B1,6) you
use the whole range
and with the exception of sumproduct all these formulas need to be entered
that way,
you can lookup array in help

--

Regards,

Peo Sjoblom

"KIM" <[email protected]> wrote in message
news:[email protected]...
> Sorry guys just tried both formulas again but finished by pressing ctrl +
> shift & enter and it has worked. Sorry novice about - what does ctrl +
shift
> & enter do?
>
> Thank you

The formula worked when I had all the cells filled with an entry (as you
said) however in some of my other data I have some blank cells too so Answer
2 and 3 won't work. Is there anyway round this?

example data
0.01

0.001
<0.001

0.01
<0.1

i.e I would like the average of all the numbers = 0.0244

Just to confuse the issue I want to link the data into an access database.
The problem with this is that access cannot cope with information in mixed
formates (i.e. a field can only be numerical or text) The only way around
this I have found so far it to trick access into believing that all the data
is text by inserting ' infront of everything (numbers, < and -) using a
macro. So will having ' infornt of all the entries affect the answer to the
above question?

Sorry this is so complicated

Thank you

"Bernie Deitrick" wrote:

> KIM,
>
> Answer 1:
>
> =AVERAGE(A1:A10)
>
> But this ignores the cells with the < signed values. For your example, this
> will return 11.
>
> Answer 2:
>
> Array enter (enter using Ctrl-Shift-Enter) the formula
>
> =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
>
> where A1:A10 are the cells with the values. Note, all the cells musxt be
> filled - otherwise, they will be treated as 0. For your example, this
> formula will return 7.
>
> Answer 3:
>
> Array enter (enter using Ctrl-Shift-Enter) the formula
>
> =AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> 2,15)),""),A1:A5))
>
> Though this will also ignore <1000, <500, etc. as long as the resulting
> number (to the right of the < sign) is greater than or equal to 100.
>
>
> HTH,
> Bernie
> MS Excel MVP
>
> "KIM" <[email protected]> wrote in message
> news:[email protected]...
> > Question 1. How do I average a column that contains a mixture of numbers
> and
> > less than entries?
> > e.g. 12
> > <1
> > 10
> > <5
> >
> > Question 2. Is it possible to include the less than values in the average
> > calculation (i.e so excel ignores the less than sign and calcualted the
> > avearge using the number that is next to the less than sign)?
> >
> > Question 3. Is it possible to include some less than values (e.g. <10) in
> > the average calculation but ignore say other less than values (e.g. <100)?
> >
> > Thank you!
>
>
>

One way

=SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/COUNTA(A1:A200)

if the blanks are from null strings like "" use

=SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/SUMPRODUCT(--(A1:A200<>""))

--

Regards,

Peo Sjoblom

"KIM" <[email protected]> wrote in message
news:[email protected]...
> The formula worked when I had all the cells filled with an entry (as you
> said) however in some of my other data I have some blank cells too so
Answer
> 2 and 3 won't work. Is there anyway round this?
>
> example data
> 0.01
>
> 0.001
> <0.001
>
> 0.01
> <0.1
>
> i.e I would like the average of all the numbers = 0.0244
>
> Just to confuse the issue I want to link the data into an access database.
> The problem with this is that access cannot cope with information in mixed
> formates (i.e. a field can only be numerical or text) The only way around
> this I have found so far it to trick access into believing that all the
data
> is text by inserting ' infront of everything (numbers, < and -) using a
> macro. So will having ' infornt of all the entries affect the answer to
the
> above question?
>
> Sorry this is so complicated
>
> Thank you
>
> "Bernie Deitrick" wrote:
>
> > KIM,
> >
> > Answer 1:
> >
> > =AVERAGE(A1:A10)
> >
> > But this ignores the cells with the < signed values. For your example,
this
> > will return 11.
> >
> > Answer 2:
> >
> > Array enter (enter using Ctrl-Shift-Enter) the formula
> >
> > =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
> >
> > where A1:A10 are the cells with the values. Note, all the cells musxt be
> > filled - otherwise, they will be treated as 0. For your example, this
> > formula will return 7.
> >
> > Answer 3:
> >
> > Array enter (enter using Ctrl-Shift-Enter) the formula
> >
> >
=AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> > 2,15)),""),A1:A5))
> >
> > Though this will also ignore <1000, <500, etc. as long as the resulting
> > number (to the right of the < sign) is greater than or equal to 100.
> >
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "KIM" <[email protected]> wrote in message
> > news:[email protected]...
> > > Question 1. How do I average a column that contains a mixture of
numbers
> > and
> > > less than entries?
> > > e.g. 12
> > > <1
> > > 10
> > > <5
> > >
> > > Question 2. Is it possible to include the less than values in the
average
> > > calculation (i.e so excel ignores the less than sign and calcualted
the
> > > avearge using the number that is next to the less than sign)?
> > >
> > > Question 3. Is it possible to include some less than values (e.g. <10)
in
> > > the average calculation but ignore say other less than values (e.g.
<100)?
> > >
> > > Thank you!
> >
> >
> >

Have tried both equations below and I get a #VALUE! error

Any ideas?

Thank you


"Peo Sjoblom" wrote:

> One way
>
> =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/COUNTA(A1:A200)
>
> if the blanks are from null strings like "" use
>
> =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/SUMPRODUCT(--(A1:A200<>""))
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "KIM" <[email protected]> wrote in message
> news:[email protected]...
> > The formula worked when I had all the cells filled with an entry (as you
> > said) however in some of my other data I have some blank cells too so
> Answer
> > 2 and 3 won't work. Is there anyway round this?
> >
> > example data
> > 0.01
> >
> > 0.001
> > <0.001
> >
> > 0.01
> > <0.1
> >
> > i.e I would like the average of all the numbers = 0.0244
> >
> > Just to confuse the issue I want to link the data into an access database.
> > The problem with this is that access cannot cope with information in mixed
> > formates (i.e. a field can only be numerical or text) The only way around
> > this I have found so far it to trick access into believing that all the
> data
> > is text by inserting ' infront of everything (numbers, < and -) using a
> > macro. So will having ' infornt of all the entries affect the answer to
> the
> > above question?
> >
> > Sorry this is so complicated
> >
> > Thank you
> >
> > "Bernie Deitrick" wrote:
> >
> > > KIM,
> > >
> > > Answer 1:
> > >
> > > =AVERAGE(A1:A10)
> > >
> > > But this ignores the cells with the < signed values. For your example,
> this
> > > will return 11.
> > >
> > > Answer 2:
> > >
> > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > >
> > > =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
> > >
> > > where A1:A10 are the cells with the values. Note, all the cells musxt be
> > > filled - otherwise, they will be treated as 0. For your example, this
> > > formula will return 7.
> > >
> > > Answer 3:
> > >
> > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > >
> > >
> =AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> > > 2,15)),""),A1:A5))
> > >
> > > Though this will also ignore <1000, <500, etc. as long as the resulting
> > > number (to the right of the < sign) is greater than or equal to 100.
> > >
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "KIM" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Question 1. How do I average a column that contains a mixture of
> numbers
> > > and
> > > > less than entries?
> > > > e.g. 12
> > > > <1
> > > > 10
> > > > <5
> > > >
> > > > Question 2. Is it possible to include the less than values in the
> average
> > > > calculation (i.e so excel ignores the less than sign and calcualted
> the
> > > > avearge using the number that is next to the less than sign)?
> > > >
> > > > Question 3. Is it possible to include some less than values (e.g. <10)
> in
> > > > the average calculation but ignore say other less than values (e.g.
> <100)?
> > > >
> > > > Thank you!
> > >
> > >
> > >
>
>
>

I think its because I have - in some cells in my real data but I didin't add
one in to the below example. Sorry. Is it possible even with - in some cells
or do they need removing first?

Thank you

"Peo Sjoblom" wrote:

> One way
>
> =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/COUNTA(A1:A200)
>
> if the blanks are from null strings like "" use
>
> =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/SUMPRODUCT(--(A1:A200<>""))
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "KIM" <[email protected]> wrote in message
> news:[email protected]...
> > The formula worked when I had all the cells filled with an entry (as you
> > said) however in some of my other data I have some blank cells too so
> Answer
> > 2 and 3 won't work. Is there anyway round this?
> >
> > example data
> > 0.01
> >
> > 0.001
> > <0.001
> >
> > 0.01
> > <0.1
> >
> > i.e I would like the average of all the numbers = 0.0244
> >
> > Just to confuse the issue I want to link the data into an access database.
> > The problem with this is that access cannot cope with information in mixed
> > formates (i.e. a field can only be numerical or text) The only way around
> > this I have found so far it to trick access into believing that all the
> data
> > is text by inserting ' infront of everything (numbers, < and -) using a
> > macro. So will having ' infornt of all the entries affect the answer to
> the
> > above question?
> >
> > Sorry this is so complicated
> >
> > Thank you
> >
> > "Bernie Deitrick" wrote:
> >
> > > KIM,
> > >
> > > Answer 1:
> > >
> > > =AVERAGE(A1:A10)
> > >
> > > But this ignores the cells with the < signed values. For your example,
> this
> > > will return 11.
> > >
> > > Answer 2:
> > >
> > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > >
> > > =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
> > >
> > > where A1:A10 are the cells with the values. Note, all the cells musxt be
> > > filled - otherwise, they will be treated as 0. For your example, this
> > > formula will return 7.
> > >
> > > Answer 3:
> > >
> > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > >
> > >
> =AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> > > 2,15)),""),A1:A5))
> > >
> > > Though this will also ignore <1000, <500, etc. as long as the resulting
> > > number (to the right of the < sign) is greater than or equal to 100.
> > >
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "KIM" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Question 1. How do I average a column that contains a mixture of
> numbers
> > > and
> > > > less than entries?
> > > > e.g. 12
> > > > <1
> > > > 10
> > > > <5
> > > >
> > > > Question 2. Is it possible to include the less than values in the
> average
> > > > calculation (i.e so excel ignores the less than sign and calcualted
> the
> > > > avearge using the number that is next to the less than sign)?
> > > >
> > > > Question 3. Is it possible to include some less than values (e.g. <10)
> in
> > > > the average calculation but ignore say other less than values (e.g.
> <100)?
> > > >
> > > > Thank you!
> > >
> > >
> > >
>
>
>

Then you have other text in the range, I just tried it on your sample and
got 0.0244
Btw, why are you using this? Import from another program?
You can use a help column

=IF(ISNUMBER(FIND("<",A1)),--SUBSTITUTE(A1,"<",""),IF(ISNUMBER(A1),A1,""))

copy down as long as needed and then use a regular average, or the array
formula


=SUM((IF(ISNUMBER(FIND("<",A1:A200)),--SUBSTITUTE(A1:A200,"<",""),IF(ISNUMBE
R(A1:A200),A1:A200,""))))/(COUNT(A1:A200)+SUMPRODUCT(--(ISNUMBER(FIND("<",A1
:A200)))))

but it's getting more and more complicated so I would personally use the
help column


--

Regards,

Peo Sjoblom




"KIM" <[email protected]> wrote in message
news:[email protected]...
> Have tried both equations below and I get a #VALUE! error
>
> Any ideas?
>
> Thank you
>
>
> "Peo Sjoblom" wrote:
>
> > One way
> >
> > =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/COUNTA(A1:A200)
> >
> > if the blanks are from null strings like "" use
> >
> >
=SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/SUMPRODUCT(--(A1:A200<>""))
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "KIM" <[email protected]> wrote in message
> > news:[email protected]...
> > > The formula worked when I had all the cells filled with an entry (as
you
> > > said) however in some of my other data I have some blank cells too so
> > Answer
> > > 2 and 3 won't work. Is there anyway round this?
> > >
> > > example data
> > > 0.01
> > >
> > > 0.001
> > > <0.001
> > >
> > > 0.01
> > > <0.1
> > >
> > > i.e I would like the average of all the numbers = 0.0244
> > >
> > > Just to confuse the issue I want to link the data into an access
database.
> > > The problem with this is that access cannot cope with information in
mixed
> > > formates (i.e. a field can only be numerical or text) The only way
around
> > > this I have found so far it to trick access into believing that all
the
> > data
> > > is text by inserting ' infront of everything (numbers, < and -) using
a
> > > macro. So will having ' infornt of all the entries affect the answer
to
> > the
> > > above question?
> > >
> > > Sorry this is so complicated
> > >
> > > Thank you
> > >
> > > "Bernie Deitrick" wrote:
> > >
> > > > KIM,
> > > >
> > > > Answer 1:
> > > >
> > > > =AVERAGE(A1:A10)
> > > >
> > > > But this ignores the cells with the < signed values. For your
example,
> > this
> > > > will return 11.
> > > >
> > > > Answer 2:
> > > >
> > > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > > >
> > > > =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
> > > >
> > > > where A1:A10 are the cells with the values. Note, all the cells
musxt be
> > > > filled - otherwise, they will be treated as 0. For your example,
this
> > > > formula will return 7.
> > > >
> > > > Answer 3:
> > > >
> > > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > > >
> > > >
> >
=AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> > > > 2,15)),""),A1:A5))
> > > >
> > > > Though this will also ignore <1000, <500, etc. as long as the
resulting
> > > > number (to the right of the < sign) is greater than or equal to 100.
> > > >
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > > "KIM" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Question 1. How do I average a column that contains a mixture of
> > numbers
> > > > and
> > > > > less than entries?
> > > > > e.g. 12
> > > > > <1
> > > > > 10
> > > > > <5
> > > > >
> > > > > Question 2. Is it possible to include the less than values in the
> > average
> > > > > calculation (i.e so excel ignores the less than sign and
calcualted
> > the
> > > > > avearge using the number that is next to the less than sign)?
> > > > >
> > > > > Question 3. Is it possible to include some less than values (e.g.
<10)
> > in
> > > > > the average calculation but ignore say other less than values
(e.g.
> > <100)?
> > > > >
> > > > > Thank you!
> > > >
> > > >
> > > >
> >
> >
> >

Yeap that worked - although I am completely out of my depth in understanding
what the formula does (any idea where I might get some training?). What am I
trying to do it all for - good question!

I have a excel spreadsheet with chemical analyses data (over 25 different
tests) for water samples collected from several boreholes collected over the
past 3 years and is continuing to be collected every month. We would like to
use access to sort the data into reports showing different boreholes or
different analyses results or different dates depending on what we need for a
report. If we did this in excel it would mean re-grouping the data everytime
we had new data or we wanted a different combination of the data (e.g. a
table showing BH10 for all dates and all chemical analyses or BH20 just 2003
results or BH1 just sodium concentration and BOD results). Part of the
problem is that when we recieve this data from the lab the sheets contain <
values for results below the limits of detection or - where a sample has not
been scheduled. We need to keep all the < entries as they are to go into the
reports. So several problems
1) I may want average (for a report) of all the true number results for
Sodium concentration (ignoring <, - and empty cells)
2) I want to average all true number and < results for sodium concentrations
(ignoring - and empty cells)
BUT this is compleicated if I want to link this spreadsheet to access - if
it is linked (rather than imported) the data will automatically update in
access when new data is added into excel. Access cannot cope with mixed data
types (eg TEXT and NUMERICAL) in the same field. Therefore the only way I
have managed to trick access into beliveing that all the data is TEXT is by
using a macro to enter ' infront of every entry (number, <, and -). Which as
a results complicates the average calculations.

So, I need to:
- format the excel sheet to link to access
- calculate averages for numbers (ignoring <, - and empty cells) irrelevant
of access formatting
- calculate averages for numbers and < (ignoring - and empty cells)
irrelevant of access formatting
- use access to create reports of various combinations of all the data I have

i.e. one complex headache

Any thoughts?




"Peo Sjoblom" wrote:

> Then you have other text in the range, I just tried it on your sample and
> got 0.0244
> Btw, why are you using this? Import from another program?
> You can use a help column
>
> =IF(ISNUMBER(FIND("<",A1)),--SUBSTITUTE(A1,"<",""),IF(ISNUMBER(A1),A1,""))
>
> copy down as long as needed and then use a regular average, or the array
> formula
>
>
> =SUM((IF(ISNUMBER(FIND("<",A1:A200)),--SUBSTITUTE(A1:A200,"<",""),IF(ISNUMBE
> R(A1:A200),A1:A200,""))))/(COUNT(A1:A200)+SUMPRODUCT(--(ISNUMBER(FIND("<",A1
> :A200)))))
>
> but it's getting more and more complicated so I would personally use the
> help column
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
>
> "KIM" <[email protected]> wrote in message
> news:[email protected]...
> > Have tried both equations below and I get a #VALUE! error
> >
> > Any ideas?
> >
> > Thank you
> >
> >
> > "Peo Sjoblom" wrote:
> >
> > > One way
> > >
> > > =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/COUNTA(A1:A200)
> > >
> > > if the blanks are from null strings like "" use
> > >
> > >
> =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/SUMPRODUCT(--(A1:A200<>""))
> > >
> > > --
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "KIM" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > The formula worked when I had all the cells filled with an entry (as
> you
> > > > said) however in some of my other data I have some blank cells too so
> > > Answer
> > > > 2 and 3 won't work. Is there anyway round this?
> > > >
> > > > example data
> > > > 0.01
> > > >
> > > > 0.001
> > > > <0.001
> > > >
> > > > 0.01
> > > > <0.1
> > > >
> > > > i.e I would like the average of all the numbers = 0.0244
> > > >
> > > > Just to confuse the issue I want to link the data into an access
> database.
> > > > The problem with this is that access cannot cope with information in
> mixed
> > > > formates (i.e. a field can only be numerical or text) The only way
> around
> > > > this I have found so far it to trick access into believing that all
> the
> > > data
> > > > is text by inserting ' infront of everything (numbers, < and -) using
> a
> > > > macro. So will having ' infornt of all the entries affect the answer
> to
> > > the
> > > > above question?
> > > >
> > > > Sorry this is so complicated
> > > >
> > > > Thank you
> > > >
> > > > "Bernie Deitrick" wrote:
> > > >
> > > > > KIM,
> > > > >
> > > > > Answer 1:
> > > > >
> > > > > =AVERAGE(A1:A10)
> > > > >
> > > > > But this ignores the cells with the < signed values. For your
> example,
> > > this
> > > > > will return 11.
> > > > >
> > > > > Answer 2:
> > > > >
> > > > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > > > >
> > > > > =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
> > > > >
> > > > > where A1:A10 are the cells with the values. Note, all the cells
> musxt be
> > > > > filled - otherwise, they will be treated as 0. For your example,
> this
> > > > > formula will return 7.
> > > > >
> > > > > Answer 3:
> > > > >
> > > > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > > > >
> > > > >
> > >
> =AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> > > > > 2,15)),""),A1:A5))
> > > > >
> > > > > Though this will also ignore <1000, <500, etc. as long as the
> resulting
> > > > > number (to the right of the < sign) is greater than or equal to 100.
> > > > >
> > > > >
> > > > > HTH,
> > > > > Bernie
> > > > > MS Excel MVP
> > > > >
> > > > > "KIM" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Question 1. How do I average a column that contains a mixture of
> > > numbers
> > > > > and
> > > > > > less than entries?
> > > > > > e.g. 12
> > > > > > <1
> > > > > > 10
> > > > > > <5
> > > > > >
> > > > > > Question 2. Is it possible to include the less than values in the
> > > average
> > > > > > calculation (i.e so excel ignores the less than sign and
> calcualted
> > > the
> > > > > > avearge using the number that is next to the less than sign)?
> > > > > >
> > > > > > Question 3. Is it possible to include some less than values (e.g.
> <10)
> > > in
> > > > > > the average calculation but ignore say other less than values
> (e.g.
> > > <100)?
> > > > > >
> > > > > > Thank you!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Err thought it worked - it gave me an answer but when I calculated the
average having taken out the ' , < and - I got a different answer - see below

<0.3 0.3
1.1 1.1
<0.3 0.3
<0.1 0.1
< 0.1 0.1
< 0.1 0.1
< 0.1 0.1
<0.1 0.1
<0.1 0.1
<0.1 0.1
<0.1 0.1
<0.1 0.1
<0.1 0.1
0.1 0.1
0.1 0.1
<0.1 0.1

<0.1 0.1
<0.1 0.1

0.1 0.1
-
AVERAGE 0.127 0.174

I think I give up!


"KIM" wrote:

> Yeap that worked - although I am completely out of my depth in understanding
> what the formula does (any idea where I might get some training?). What am I
> trying to do it all for - good question!
>
> I have a excel spreadsheet with chemical analyses data (over 25 different
> tests) for water samples collected from several boreholes collected over the
> past 3 years and is continuing to be collected every month. We would like to
> use access to sort the data into reports showing different boreholes or
> different analyses results or different dates depending on what we need for a
> report. If we did this in excel it would mean re-grouping the data everytime
> we had new data or we wanted a different combination of the data (e.g. a
> table showing BH10 for all dates and all chemical analyses or BH20 just 2003
> results or BH1 just sodium concentration and BOD results). Part of the
> problem is that when we recieve this data from the lab the sheets contain <
> values for results below the limits of detection or - where a sample has not
> been scheduled. We need to keep all the < entries as they are to go into the
> reports. So several problems
> 1) I may want average (for a report) of all the true number results for
> Sodium concentration (ignoring <, - and empty cells)
> 2) I want to average all true number and < results for sodium concentrations
> (ignoring - and empty cells)
> BUT this is compleicated if I want to link this spreadsheet to access - if
> it is linked (rather than imported) the data will automatically update in
> access when new data is added into excel. Access cannot cope with mixed data
> types (eg TEXT and NUMERICAL) in the same field. Therefore the only way I
> have managed to trick access into beliveing that all the data is TEXT is by
> using a macro to enter ' infront of every entry (number, <, and -). Which as
> a results complicates the average calculations.
>
> So, I need to:
> - format the excel sheet to link to access
> - calculate averages for numbers (ignoring <, - and empty cells) irrelevant
> of access formatting
> - calculate averages for numbers and < (ignoring - and empty cells)
> irrelevant of access formatting
> - use access to create reports of various combinations of all the data I have
>
> i.e. one complex headache
>
> Any thoughts?
>
>
>
>
> "Peo Sjoblom" wrote:
>
> > Then you have other text in the range, I just tried it on your sample and
> > got 0.0244
> > Btw, why are you using this? Import from another program?
> > You can use a help column
> >
> > =IF(ISNUMBER(FIND("<",A1)),--SUBSTITUTE(A1,"<",""),IF(ISNUMBER(A1),A1,""))
> >
> > copy down as long as needed and then use a regular average, or the array
> > formula
> >
> >
> > =SUM((IF(ISNUMBER(FIND("<",A1:A200)),--SUBSTITUTE(A1:A200,"<",""),IF(ISNUMBE
> > R(A1:A200),A1:A200,""))))/(COUNT(A1:A200)+SUMPRODUCT(--(ISNUMBER(FIND("<",A1
> > :A200)))))
> >
> > but it's getting more and more complicated so I would personally use the
> > help column
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> >
> >
> > "KIM" <[email protected]> wrote in message
> > news:[email protected]...
> > > Have tried both equations below and I get a #VALUE! error
> > >
> > > Any ideas?
> > >
> > > Thank you
> > >
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > One way
> > > >
> > > > =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/COUNTA(A1:A200)
> > > >
> > > > if the blanks are from null strings like "" use
> > > >
> > > >
> > =SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/SUMPRODUCT(--(A1:A200<>""))
> > > >
> > > > --
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > "KIM" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > The formula worked when I had all the cells filled with an entry (as
> > you
> > > > > said) however in some of my other data I have some blank cells too so
> > > > Answer
> > > > > 2 and 3 won't work. Is there anyway round this?
> > > > >
> > > > > example data
> > > > > 0.01
> > > > >
> > > > > 0.001
> > > > > <0.001
> > > > >
> > > > > 0.01
> > > > > <0.1
> > > > >
> > > > > i.e I would like the average of all the numbers = 0.0244
> > > > >
> > > > > Just to confuse the issue I want to link the data into an access
> > database.
> > > > > The problem with this is that access cannot cope with information in
> > mixed
> > > > > formates (i.e. a field can only be numerical or text) The only way
> > around
> > > > > this I have found so far it to trick access into believing that all
> > the
> > > > data
> > > > > is text by inserting ' infront of everything (numbers, < and -) using
> > a
> > > > > macro. So will having ' infornt of all the entries affect the answer
> > to
> > > > the
> > > > > above question?
> > > > >
> > > > > Sorry this is so complicated
> > > > >
> > > > > Thank you
> > > > >
> > > > > "Bernie Deitrick" wrote:
> > > > >
> > > > > > KIM,
> > > > > >
> > > > > > Answer 1:
> > > > > >
> > > > > > =AVERAGE(A1:A10)
> > > > > >
> > > > > > But this ignores the cells with the < signed values. For your
> > example,
> > > > this
> > > > > > will return 11.
> > > > > >
> > > > > > Answer 2:
> > > > > >
> > > > > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > > > > >
> > > > > > =AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2,15)),A1:A10))
> > > > > >
> > > > > > where A1:A10 are the cells with the values. Note, all the cells
> > musxt be
> > > > > > filled - otherwise, they will be treated as 0. For your example,
> > this
> > > > > > formula will return 7.
> > > > > >
> > > > > > Answer 3:
> > > > > >
> > > > > > Array enter (enter using Ctrl-Shift-Enter) the formula
> > > > > >
> > > > > >
> > > >
> > =AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2,15))<100,VALUE(MID(A1:A5,
> > > > > > 2,15)),""),A1:A5))
> > > > > >
> > > > > > Though this will also ignore <1000, <500, etc. as long as the
> > resulting
> > > > > > number (to the right of the < sign) is greater than or equal to 100.
> > > > > >
> > > > > >
> > > > > > HTH,
> > > > > > Bernie
> > > > > > MS Excel MVP
> > > > > >
> > > > > > "KIM" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > Question 1. How do I average a column that contains a mixture of
> > > > numbers
> > > > > > and
> > > > > > > less than entries?
> > > > > > > e.g. 12
> > > > > > > <1
> > > > > > > 10
> > > > > > > <5
> > > > > > >
> > > > > > > Question 2. Is it possible to include the less than values in the
> > > > average
> > > > > > > calculation (i.e so excel ignores the less than sign and
> > calcualted
> > > > the
> > > > > > > avearge using the number that is next to the less than sign)?
> > > > > > >
> > > > > > > Question 3. Is it possible to include some less than values (e.g.
> > <10)
> > > > in
> > > > > > > the average calculation but ignore say other less than values
> > (e.g.
> > > > <100)?
> > > > > > >
> > > > > > > Thank you!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >