I have a spread sheet in which laboratory results are entered. So of the
results are less than numbers I do not want to include the less than
results in the average how do I write a formula for this. I am using
excel2003.?



Example: 21
16
39
<10
<10


--
hamricka
------------------------------------------------------------------------
hamricka's Profile: http://www.thecodecage.com/forumz/member.php?userid=676
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542

excel will not see <10 as a number, it will see it as text. so when
you average the column, it is automatically excluded (from what i
could see in a trial).
however an average of 16 & 39 came out to 27.5, not 21 as you have
indicated (averaged both with the less-than "numbers" in the range &
without).
hope that helps.
susan


On Aug 14, 3:39*pm, hamricka wrote:
I have a spread sheet in which laboratory results are entered. So of the
results are less than numbers I do not want to include the less than
results in the average how do I write a formula for this. I am using
excel2003.?

Example: * * * *21
16
39
<10
<10

--
hamricka
------------------------------------------------------------------------
hamricka's Profile:http://www.thecodecage.com/forumz/member.php?userid=676
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=125542

I need to correct my statement the collumn contains 21, 16, 39, <10, <10.
When I average this collumun I need to count place holders for the < values,
so it would be like adding 21, 16, an 39 then dividing by five, but the total
number of values in the collum could range from 0 to 31 on any day with some
of them being < than figures.

"hamricka" wrote:


I have a spread sheet in which laboratory results are entered. So of the
results are less than numbers I do not want to include the less than
results in the average how do I write a formula for this. I am using
excel2003.?



Example:
21
16
39
<10
<10


--
hamricka
------------------------------------------------------------------------
hamricka's Profile: http://www.thecodecage.com/forumz/member.php?userid=676
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542

hamricka;453508 Wrote:
I have a spread sheet in which laboratory results are entered. So of the
results are less than numbers I do not want to include the less than
results in the average how do I write a formula for this. I am using
excel2003.?



Example: 21
16
39
<10
<10

If you are trying to avoid the 2 "<10" in your average, then try:

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))

where A1:A5 is the whole range.

Note: The fomrula must be confirmed with CTRL+SHIFT+ENTER not just
ENTER. You will see { } brackets appear around it


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542

Clarification on Function Question;453545 Wrote:
I need to correct my statement the collumn contains 21, 16, 39, <10,
<10.
When I average this collumun I need to count place holders for the <
values,
so it would be like adding 21, 16, an 39 then dividing by five, but the
total
number of values in the collum could range from 0 to 31 on any day with
some
of them being < than figures.

"hamricka" wrote:


I have a spread sheet in which laboratory results are entered. So of
the
results are less than numbers I do not want to include the less than
results in the average how do I write a formula for this. I am using
excel2003.?



Example:
21
16
39
<10
<10


--
hamricka

------------------------------------------------------------------------
hamricka's Profile: 'The Code Cage Forums - View Profile: hamricka'
(http://www.thecodecage.com/forumz/members/hamricka.html)
View this thread: 'Forumla Question for Excel2003 - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=125542)



Then perhaps:

=SUMPRODUCT(--(ISNUMBER(A1:A5)),A1:A5)/COUNTA(A1:A5)

regularly entered.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542

Good try but the answer still comes up as 19.2 and I need it to be 15.2. Here
is the collumn as it appears on the form maybe that will help. These are test
results for the month of June. The formula needs to be able to adjust for the
number of days of the month but count the < numbers as 0 in the average. I
have tried everthing I can think of please give it another try.

50060
RESIDUAL CHLORINE
mg/L
UG/L

16.00








21.00




39.00






< 10.00






< 10.00

19.20
39.00 39.00
10.00 10.00
G


"NBVC" wrote:


hamricka;453508 Wrote:
I have a spread sheet in which laboratory results are entered. So of the
results are less than numbers I do not want to include the less than
results in the average how do I write a formula for this. I am using
excel2003.?



Example: 21
16
39
<10
<10

If you are trying to avoid the 2 "<10" in your average, then try:

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))

where A1:A5 is the whole range.

Note: The fomrula must be confirmed with CTRL+SHIFT+ENTER not just
ENTER. You will see { } brackets appear around it


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542

Clarification on Function Question;453635 Wrote:
Good try but the answer still comes up as 19.2 and I need it to be 15.2.
Here
is the collumn as it appears on the form maybe that will help. These
are test
results for the month of June. The formula needs to be able to adjust
for the
number of days of the month but count the < numbers as 0 in the
average. I
have tried everthing I can think of please give it another try.

50060
RESIDUAL CHLORINE
mg/L
UG/L

16.00








21.00




39.00






< 10.00






< 10.00

19.20
39.00 39.00
10.00 10.00
G


"NBVC" wrote:


hamricka;453508 Wrote:
I have a spread sheet in which laboratory results are entered. So
of the
results are less than numbers I do not want to include the less
than
results in the average how do I write a formula for this. I am
using
excel2003.?



Example: 21
16
39
<10
<10

If you are trying to avoid the 2 "<10" in your average, then try:

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))

where A1:A5 is the whole range.

Note: The fomrula must be confirmed with CTRL+SHIFT+ENTER not just
ENTER. You will see { } brackets appear around it


--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread: 'Forumla Question for Excel2003 - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=125542)



This formula in my last post gives 15.2 as per your needs.

=SUMPRODUCT(--(ISNUMBER(A1:A5)),A1:A5)/COUNTA(A1:A5)


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542

Try this:

=SUM(A1:A31)/COUNTA(A1:A31)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Clarification on Function Question"
soft.com wrote in message
...
Good try but the answer still comes up as 19.2 and I need it to be 15.2.
Here
is the collumn as it appears on the form maybe that will help. These are
test
results for the month of June. The formula needs to be able to adjust for
the
number of days of the month but count the < numbers as 0 in the average. I
have tried everthing I can think of please give it another try.

50060
RESIDUAL CHLORINE
mg/L
UG/L

16.00








21.00




39.00






< 10.00






< 10.00

19.20
39.00 39.00
10.00 10.00
G


"NBVC" wrote:


hamricka;453508 Wrote:
I have a spread sheet in which laboratory results are entered. So of
the
results are less than numbers I do not want to include the less than
results in the average how do I write a formula for this. I am using
excel2003.?



Example: 21
16
39
<10
<10

If you are trying to avoid the 2 "<10" in your average, then try:

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))

where A1:A5 is the whole range.

Note: The fomrula must be confirmed with CTRL+SHIFT+ENTER not just
ENTER. You will see { } brackets appear around it


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=125542

NBVC;453641 Wrote:
This formula in my last post gives 15.2 as per your needs.

=SUMPRODUCT(--(ISNUMBER(A1:A5)),A1:A5)/COUNTA(A1:A5)

You are correct it was an error on my part. Thank you so much you saved
me lost of hours of hand typing results.Bg:)


--
hamricka
------------------------------------------------------------------------
hamricka's Profile: http://www.thecodecage.com/forumz/member.php?userid=676
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542

Glad we could be of help!


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125542