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.

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.

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.

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

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

=IF(COUNTIF(A1:A3,"*<*")0,"<","")&ROUND(AVERAGE(I F(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


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


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

"Ken Johnson" wrote in message
...
On Apr 23, 7:04 am, Pedro S. wrote:

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

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


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

Hi Sandy,

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

Ken Johnson

Thank very much for the great help...

"Sandy Mann" wrote:

"Ken Johnson" wrote in message
...
On Apr 23, 7:04 am, Pedro S. wrote:

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

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


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

Thank very much for the great help...

"Sandy Mann" wrote:

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

=IF(COUNTIF(A1:A3,"*<*")0,"<","")&ROUND(AVERAGE(I F(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


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


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