"Greater than" and "lower than"

[Pedro S.]

How can I get the “<†and “>†symbols act as part of a number on a formula?

I work at a lab, and when we get results that our equipment can not measure
the samples, we use the “less than†symbol… The problem is that when you
write a formula at the end of the column; for example get the average of
A1:A22, if any of the cells contains a “<†symbol, the result should also
have “<†with the number.

 

[akphidelt]

Try instead of using average...

=(Sumif(A1:A22,">0",A1:A22)/Countif(A1:A22,">0"))

This sums all values that are numbers, then divides by the count of all
values that are numbers... which gives you the average

 

[Sandy Mann]

Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number and let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift + Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number and let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

How can I get the "<" and ">" symbols act as part of a number on a
formula?

I work at a lab, and when we get results that our equipment can not
measure
the samples, we use the "less than" symbol. The problem is that when you
write a formula at the end of the column; for example get the average of
A1:A22, if any of the cells contains a "<" symbol, the result should also
have "<" with the number.

 

[Pedro S.]

Sandy Mann,

Lets see if i explain better my situation.

2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0

All i need is excel to add "<" to the result if any of the numbers in the
colum has that "<" symbol.

Any sugestions

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift + Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number and let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

How can I get the "<" and ">" symbols act as part of a number on a
formula?

I work at a lab, and when we get results that our equipment can not
measure
the samples, we use the "less than" symbol. The problem is that when you
write a formula at the end of the column; for example get the average of
A1:A22, if any of the cells contains a "<" symbol, the result should also
have "<" with the number.

 

[Sandy Mann]

Would a UDF do? If so try::

Option Explicit
Function SumIt(rRange As Range)
Dim sValue As Boolean
Dim Tot As Variant
Dim Temp As Variant
Dim cCount As Integer
Dim cell As Range

Application.Volatile

For Each cell In rRange
Temp = cell.Value
If Left(Temp, 1) = "<" Then
Temp = CDec(Right(Temp, Len(Temp) - 1))
sValue = True
End If

Tot = Tot + Temp
cCount = cCount + 1
Next cell

Tot = Tot / cCount

Tot = Int(Tot * 100 + 0.5) / 100

If sValue = True Then Tot = "<" & CStr(Tot)

SumIt = Tot

End Function

This will give you <3 for your values.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy Mann,

Lets see if i explain better my situation.

2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0

All i need is excel to add "<" to the result if any of the numbers in the
colum has that "<" symbol.

Any sugestions

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift +
Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number and
let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


How can I get the "<" and ">" symbols act as part of a number on a
formula?

I work at a lab, and when we get results that our equipment can not
measure
the samples, we use the "less than" symbol. The problem is that when
you
write a formula at the end of the column; for example get the average
of
A1:A22, if any of the cells contains a "<" symbol, the result should
also
have "<" with the number.

 

[Pedro S.]

Sandy Mann,

I am not as good in Excel, like you. Do you care in elavorate on this?

I created the UDF and when i get out of the VB window, i dont see the
results on the sheet...

Would a UDF do? If so try::

Option Explicit
Function SumIt(rRange As Range)
Dim sValue As Boolean
Dim Tot As Variant
Dim Temp As Variant
Dim cCount As Integer
Dim cell As Range

Application.Volatile

For Each cell In rRange
Temp = cell.Value
If Left(Temp, 1) = "<" Then
Temp = CDec(Right(Temp, Len(Temp) - 1))
sValue = True
End If

Tot = Tot + Temp
cCount = cCount + 1
Next cell

Tot = Tot / cCount

Tot = Int(Tot * 100 + 0.5) / 100

If sValue = True Then Tot = "<" & CStr(Tot)

SumIt = Tot

End Function

This will give you <3 for your values.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy Mann,

Lets see if i explain better my situation.

2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0

All i need is excel to add "<" to the result if any of the numbers in the
colum has that "<" symbol.

Any sugestions

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift +
Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number and
let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


How can I get the "<" and ">" symbols act as part of a number on a
formula?

I work at a lab, and when we get results that our equipment can not
measure
the samples, we use the "less than" symbol. The problem is that when
you
write a formula at the end of the column; for example get the average
of
A1:A22, if any of the cells contains a "<" symbol, the result should
also
have "<" with the number.

 

[Sandy Mann]

You use the UDF just like a function. For example of your sample data is in
A1:A3 then in A4 enter:

=(SumIt(A1:A3)

and press Enter. But Ken's formula is a better option.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy Mann,

I am not as good in Excel, like you. Do you care in elavorate on this?

I created the UDF and when i get out of the VB window, i dont see the
results on the sheet...

Would a UDF do? If so try::

Option Explicit
Function SumIt(rRange As Range)
Dim sValue As Boolean
Dim Tot As Variant
Dim Temp As Variant
Dim cCount As Integer
Dim cell As Range

Application.Volatile

For Each cell In rRange
Temp = cell.Value
If Left(Temp, 1) = "<" Then
Temp = CDec(Right(Temp, Len(Temp) - 1))
sValue = True
End If

Tot = Tot + Temp
cCount = cCount + 1
Next cell

Tot = Tot / cCount

Tot = Int(Tot * 100 + 0.5) / 100

If sValue = True Then Tot = "<" & CStr(Tot)

SumIt = Tot

End Function

This will give you <3 for your values.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy Mann,

Lets see if i explain better my situation.

2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0

All i need is excel to add "<" to the result if any of the numbers in
the
colum has that "<" symbol.

Any sugestions

:

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift +
Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number
and
let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


How can I get the "<" and ">" symbols act as part of a number on a
formula?

I work at a lab, and when we get results that our equipment can not
measure
the samples, we use the "less than" symbol. The problem is that
when
you
write a formula at the end of the column; for example get the
average
of
A1:A22, if any of the cells contains a "<" symbol, the result
should
also
have "<" with the number.

 

[Pedro S.]

I see it now. And you are correct. Ken's way looks great, but my only problem
now is how to control the decimal points on the cell with the results.

My actual numbers on my sheets are a series of 0.02 and <0.02 for everyday
of the month and the result i am getting is < 0.0142857142857143. wich it is
the correct anwser, but i need to round it up to 2 decimal points.

I did try to format the cell and reduce the decimal points an it did not
work...

You use the UDF just like a function. For example of your sample data is in
A1:A3 then in A4 enter:

=(SumIt(A1:A3)

and press Enter. But Ken's formula is a better option.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy Mann,

I am not as good in Excel, like you. Do you care in elavorate on this?

I created the UDF and when i get out of the VB window, i dont see the
results on the sheet...

Would a UDF do? If so try::

Option Explicit
Function SumIt(rRange As Range)
Dim sValue As Boolean
Dim Tot As Variant
Dim Temp As Variant
Dim cCount As Integer
Dim cell As Range

Application.Volatile

For Each cell In rRange
Temp = cell.Value
If Left(Temp, 1) = "<" Then
Temp = CDec(Right(Temp, Len(Temp) - 1))
sValue = True
End If

Tot = Tot + Temp
cCount = cCount + 1
Next cell

Tot = Tot / cCount

Tot = Int(Tot * 100 + 0.5) / 100

If sValue = True Then Tot = "<" & CStr(Tot)

SumIt = Tot

End Function

This will give you <3 for your values.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy Mann,

Lets see if i explain better my situation.

2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0

All i need is excel to add "<" to the result if any of the numbers in
the
colum has that "<" symbol.

Any sugestions

:

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift +
Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number
and
let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


How can I get the "<" and ">" symbols act as part of a number on a
formula?

I work at a lab, and when we get results that our equipment can not
measure
the samples, we use the "less than" symbol. The problem is that
when
you
write a formula at the end of the column; for example get the
average
of
A1:A22, if any of the cells contains a "<" symbol, the result
should
also
have "<" with the number.

 

[Ken Johnson]

I see it now. And you are correct. Ken's way looks great, but my only problem
now is how to control the decimal points on the cell with the results.

My actual numbers on my sheets are a series of 0.02 and <0.02 for everyday
of the month and the result i am getting is < 0.0142857142857143. wich it is
the correct anwser, but i need to round it up to 2 decimal points.

I did try to format the cell and reduce the decimal points an it did not
work...

You use the UDF just like a function. For example of your sample data is in
A1:A3 then in A4 enter:

and press Enter. But Ken's formula is a better option.
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy Mann,
I am not as good in Excel, like you. Do you care in elavorate on this?
I created the UDF and when i get out of the VB window, i dont see the
results on the sheet...
:
Would a UDF do? If so try::
Option Explicit
Function SumIt(rRange As Range)
Dim sValue As Boolean
Dim Tot As Variant
Dim Temp As Variant
Dim cCount As Integer
Dim cell As Range
Application.Volatile
For Each cell In rRange
Temp = cell.Value
If Left(Temp, 1) = "<" Then
Temp = CDec(Right(Temp, Len(Temp) - 1))
sValue = True
End If
Tot = Tot + Temp
cCount = cCount + 1
Next cell
Tot = Tot / cCount
Tot = Int(Tot * 100 + 0.5) / 100
If sValue = True Then Tot = "<" & CStr(Tot)
SumIt = Tot
End Function
This will give you <3 for your values.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Sandy Mann,
Lets see if i explain better my situation.
2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0
All i need is excel to add "<" to the result if any of the numbers in
the
colum has that "<" symbol.
Any sugestions
:
Mmmmmmm.......
Doesn't do what you want in at least three ways.
Try:
=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))
which is an array formula which should be entered with Ctrl + Shift +
Enter
not just Enter.
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Try a Custom format of
[<1]"<"#;General
change the <1 to your limit and enter the number as a real number
and
let
the formatting deal with it.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
How can I get the "<" and ">" symbols act as part of a number on a
formula?
I work at a lab, and when we get results that our equipment can not
measure
the samples, we use the "less than" symbol. The problem is that
when
you
write a formula at the end of the column; for example get the
average
of
A1:A22, if any of the cells contains a "<" symbol, the result
should
also
have "<" with the number.

Hi Pedro,

=IF(COUNTIF(A1:A3,"*<*")>0,"<","")
&ROUND(AVERAGE(IF(LEFT(A1:A3,1)="<",VALUE(MID(A1:A3,2,255)),A1:A3)),2)

for two dp

Ken Johnson

 

[Sandy Mann]

If I may be impertinent enough to modify Ken's formula try:

=IF(COUNTIF(A1:A3,"*<*")>0,"<","")&ROUND(AVERAGE(IF(LEFT(A1:A3,1)="<",VALUE(MID(A1:A3,2,255)),A1:A3)),2)

Remember that it is still an array formula and must be entered with Ctrl +
Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

I see it now. And you are correct. Ken's way looks great, but my only
problem
now is how to control the decimal points on the cell with the results.

My actual numbers on my sheets are a series of 0.02 and <0.02 for everyday
of the month and the result i am getting is < 0.0142857142857143. wich it
is
the correct anwser, but i need to round it up to 2 decimal points.

I did try to format the cell and reduce the decimal points an it did not
work...

You use the UDF just like a function. For example of your sample data is
in
A1:A3 then in A4 enter:

=(SumIt(A1:A3)

and press Enter. But Ken's formula is a better option.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Sandy Mann,

I am not as good in Excel, like you. Do you care in elavorate on this?

I created the UDF and when i get out of the VB window, i dont see the
results on the sheet...

:

Would a UDF do? If so try::

Option Explicit
Function SumIt(rRange As Range)
Dim sValue As Boolean
Dim Tot As Variant
Dim Temp As Variant
Dim cCount As Integer
Dim cell As Range

Application.Volatile

For Each cell In rRange
Temp = cell.Value
If Left(Temp, 1) = "<" Then
Temp = CDec(Right(Temp, Len(Temp) - 1))
sValue = True
End If

Tot = Tot + Temp
cCount = cCount + 1
Next cell

Tot = Tot / cCount

Tot = Int(Tot * 100 + 0.5) / 100

If sValue = True Then Tot = "<" & CStr(Tot)

SumIt = Tot

End Function

This will give you <3 for your values.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy Mann,

Lets see if i explain better my situation.

2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0

All i need is excel to add "<" to the result if any of the numbers
in
the
colum has that "<" symbol.

Any sugestions

:

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift
+
Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number
and
let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


How can I get the "<" and ">" symbols act as part of a number on
a
formula?

I work at a lab, and when we get results that our equipment can
not
measure
the samples, we use the "less than" symbol. The problem is that
when
you
write a formula at the end of the column; for example get the
average
of
A1:A22, if any of the cells contains a "<" symbol, the result
should
also
have "<" with the number.

 

[Sandy Mann]

On Apr 23, 7:04 am, Pedro S. <[email protected]> wrote:

Hi Pedro,

=IF(COUNTIF(A1:A3,"*<*")>0,"<","")
&ROUND(AVERAGE(IF(LEFT(A1:A3,1)="<",VALUE(MID(A1:A3,2,255)),A1:A3)),2)

for two dp

Ken Johnson

Hi Ken,

You not only caught me stealing your formula but you beat me to is as well!
<g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ken Johnson]

Hi Sandy,

I thought you might have retired for the evening or taken the dog for
a walk

Ken Johnson

 

[Pedro S.]

Thank very much for the great help...

Hi Ken,

You not only caught me stealing your formula but you beat me to is as well!
<g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pedro S.]

Thank very much for the great help...

If I may be impertinent enough to modify Ken's formula try:

=IF(COUNTIF(A1:A3,"*<*")>0,"<","")&ROUND(AVERAGE(IF(LEFT(A1:A3,1)="<",VALUE(MID(A1:A3,2,255)),A1:A3)),2)

Remember that it is still an array formula and must be entered with Ctrl +
Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

I see it now. And you are correct. Ken's way looks great, but my only
problem
now is how to control the decimal points on the cell with the results.

My actual numbers on my sheets are a series of 0.02 and <0.02 for everyday
of the month and the result i am getting is < 0.0142857142857143. wich it
is
the correct anwser, but i need to round it up to 2 decimal points.

I did try to format the cell and reduce the decimal points an it did not
work...

You use the UDF just like a function. For example of your sample data is
in
A1:A3 then in A4 enter:

=(SumIt(A1:A3)

and press Enter. But Ken's formula is a better option.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy Mann,

I am not as good in Excel, like you. Do you care in elavorate on this?

I created the UDF and when i get out of the VB window, i dont see the
results on the sheet...

:

Would a UDF do? If so try::

Option Explicit
Function SumIt(rRange As Range)
Dim sValue As Boolean
Dim Tot As Variant
Dim Temp As Variant
Dim cCount As Integer
Dim cell As Range

Application.Volatile

For Each cell In rRange
Temp = cell.Value
If Left(Temp, 1) = "<" Then
Temp = CDec(Right(Temp, Len(Temp) - 1))
sValue = True
End If

Tot = Tot + Temp
cCount = cCount + 1
Next cell

Tot = Tot / cCount

Tot = Int(Tot * 100 + 0.5) / 100

If sValue = True Then Tot = "<" & CStr(Tot)

SumIt = Tot

End Function

This will give you <3 for your values.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Sandy Mann,

Lets see if i explain better my situation.

2
3
4
The average of these numbers is 3.0. But if one of the numbers is:
<2
3
4
The result should read <3.0

All i need is excel to add "<" to the result if any of the numbers
in
the
colum has that "<" symbol.

Any sugestions

:

Mmmmmmm.......

Doesn't do what you want in at least three ways.

Try:

=IF(SUM(--(ISTEXT(A1:A20)))=0,AVERAGE(A1:A20),IF(NOT(ISNA(LOOKUP("<1",A1:A20))),"<1",">1000"))

which is an array formula which should be entered with Ctrl + Shift
+
Enter
not just Enter.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Try a Custom format of

[<1]"<"#;General

change the <1 to your limit and enter the number as a real number
and
let
the formatting deal with it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


How can I get the "<" and ">" symbols act as part of a number on
a
formula?

I work at a lab, and when we get results that our equipment can
not
measure
the samples, we use the "less than" symbol. The problem is that
when
you
write a formula at the end of the column; for example get the
average
of
A1:A22, if any of the cells contains a "<" symbol, the result
should
also
have "<" with the number.