Avearging columns with less than (<) text entries

[Guest]

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!

 

[Peo Sjoblom]

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

 

[Bernie Deitrick]

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

 

[Peo Sjoblom]

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

 

[Guest]

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?

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[Peo Sjoblom]

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

 

[Guest]

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

 

[Peo Sjoblom]

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<>""))

 

[Guest]

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

Any ideas?

Thank you

 

[Guest]

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]

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

 

[Guest]

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?

 

[Guest]

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!