averaging less than values
 

How do I calculate the average, minimum, and maximum of a column of numbers
containing less than (<1) values and also some cells are empty? I want the
less than values to be calculated as zeros when averaging and the minimum
values to say <1 if it is the minimum. Example:

2
3
4

<1
<1
5

<1
4
<1

In this example the maximum is 5, the minimum is <1, and the average should
be 2.

Thanks
--
JD

averaging less than values
 

Can you use a custom number format

#,###;(#,###);"<1"

and enter 0 instead of the text value "<1"? Then you could use average,
min, and max normally while displaying "<1" for 0 values on the screen.

otherwise, you could try
=AVERAGE(IF(ISTEXT(A1:A11),0,IF(ISNUMBER(A1:A11),A 1:A11)))
array entered with Ctrl+Shift+Enter and
=IF(SUMPRODUCT(--(A1:A11="<1")),"<1",MIN(A1:A11))


"JD" wrote:

How do I calculate the average, minimum, and maximum of a column of numbers
containing less than (<1) values and also some cells are empty? I want the
less than values to be calculated as zeros when averaging and the minimum
values to say <1 if it is the minimum. Example:

2
3
4

<1
<1
5

<1
4
<1

In this example the maximum is 5, the minimum is <1, and the average should
be 2.

Thanks
--
JD