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
Pedro S. said:
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.