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.

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

"Pedro S." wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"Pedro S." <Pedro wrote in message
...
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.

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(LO OKUP("<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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Pedro S." <Pedro wrote in message
...
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,

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

"Sandy Mann" wrote:

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(LO OKUP("<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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Pedro S." <Pedro wrote in message
...
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.

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


Replace @mailinator.com with @tiscali.co.uk


"Pedro S." wrote in message
...
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

"Sandy Mann" wrote:

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(LO OKUP("<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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Pedro S." <Pedro wrote in message
...
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,

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...

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Pedro S." wrote in message
...
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

"Sandy Mann" wrote:

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(LO OKUP("<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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Pedro S." <Pedro wrote in message
...
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.