Forumla Question for Excel2003

[hamricka]

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

 

[Susan]

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

 

[Clarification on Function Question]

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.

 

[NBVC]

I have a spread sheet in which laboratory results are entered. So of th
results are less than numbers I do not want to include the less tha
results in the average how do I write a formula for this. I am usin
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 jus
ENTER. You will see { } brackets appear around i

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[NBVC]

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 th
total
number of values in the collum could range from 0 to 31 on any day wit
some
of them being < than figures.


Forums' (http://www.thecodecage.com/forumz/showthread.php?t=125542)

Then perhaps:

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

regularly entered

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Clarification on Function Question]

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]

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. Thes
are test
results for the month of June. The formula needs to be able to adjus
for the
number of days of the month but count the < numbers as 0 in th
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



Office Discussion' (http://www.thecodecage.com))

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

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

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[RagDyer]

Try this:

=SUM(A1:A31)/COUNTA(A1:A31)

 

[hamricka]

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

 

[Pecoflyer]

Glad we could be of help!


--
Pecoflyer

Cheers -

'Firefox 3.5' (http://www.mozilla.com/en-US/firefox/all-beta.html)
really IS fast !