ExcelBanter - "Greater than" and "lower than"

On Apr 23, 7:04 am, Pedro S. wrote:
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...

"Sandy Mann" wrote:
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

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

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

Hi Pedro,

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

for two dp

Ken Johnson