I have a table the list the "non-Detectable threshold" of some chemical
during an analysis... for instance there is a water sample that is tested and
the level of Nickel is listed as "<0.10" because that is as sensitive as the
instrument would allow... some levels are true number such as 1.05. Inorder
to complete my stat alalysis I need to used the actual reading (ex. 1.05)
when it exists OR i need to use a reading equiv to half of the detectable
about. So for example when the sensitivity was limited to "<0.10" I need to
use the value "0.05". How can I obtain the "0.05" value and be allowed to
copy it through all the rows???

Do I need to create a pseudo tabel where values entered as text are changed
to number values...

im so over my head... would it be more helpful if I sent an example of the
file? If so let me know how to send it to you

If the detected level or the < notation is in B1, use
=IF(ISTEXT(B1),RIGHT(B1,LEN(B1)-1)/2,B1)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Will" wrote in message
...
I have a table the list the "non-Detectable threshold" of some chemical
during an analysis... for instance there is a water sample that is tested
and
the level of Nickel is listed as "<0.10" because that is as sensitive as
the
instrument would allow... some levels are true number such as 1.05.
Inorder
to complete my stat alalysis I need to used the actual reading (ex. 1.05)
when it exists OR i need to use a reading equiv to half of the detectable
about. So for example when the sensitivity was limited to "<0.10" I need
to
use the value "0.05". How can I obtain the "0.05" value and be allowed to
copy it through all the rows???

Do I need to create a pseudo tabel where values entered as text are
changed
to number values...

im so over my head... would it be more helpful if I sent an example of the
file? If so let me know how to send it to you

Seems to me that if you remove the "<" then how do you know if the value is
the actual value or an approximate value?

You can account for this in a formula like this:

A1 = <0.10

=SUBSTITUTE(A1,"<","")/2

Biff

"Will" wrote in message
...
I have a table the list the "non-Detectable threshold" of some chemical
during an analysis... for instance there is a water sample that is tested
and
the level of Nickel is listed as "<0.10" because that is as sensitive as
the
instrument would allow... some levels are true number such as 1.05.
Inorder
to complete my stat alalysis I need to used the actual reading (ex. 1.05)
when it exists OR i need to use a reading equiv to half of the detectable
about. So for example when the sensitivity was limited to "<0.10" I need
to
use the value "0.05". How can I obtain the "0.05" value and be allowed to
copy it through all the rows???

Do I need to create a pseudo tabel where values entered as text are
changed
to number values...

im so over my head... would it be more helpful if I sent an example of the
file? If so let me know how to send it to you

Using that formula works for the first cell however when i copy the cell and
paste it down the col the values are not different even though the vlues
found int he B col change.. Also the value does not change if the formula
has been copied down the col and then I later make changes to the value found
in the field containing "<0.10"... I would liek to be able to make changes
(update) when the sensor was capable of more accurate readings... for
instance if the sensitivity was actually improved to "<0.050" then the value
i need should be able to be updated on the fly to "0.0025" because thats half
of the sesitivity level.

I hope I am explaining myself well...

"Bernard Liengme" wrote:

If the detected level or the < notation is in B1, use
=IF(ISTEXT(B1),RIGHT(B1,LEN(B1)-1)/2,B1)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Will" wrote in message
...
I have a table the list the "non-Detectable threshold" of some chemical
during an analysis... for instance there is a water sample that is tested
and
the level of Nickel is listed as "<0.10" because that is as sensitive as
the
instrument would allow... some levels are true number such as 1.05.
Inorder
to complete my stat alalysis I need to used the actual reading (ex. 1.05)
when it exists OR i need to use a reading equiv to half of the detectable
about. So for example when the sensitivity was limited to "<0.10" I need
to
use the value "0.05". How can I obtain the "0.05" value and be allowed to
copy it through all the rows???

Do I need to create a pseudo tabel where values entered as text are
changed
to number values...

im so over my head... would it be more helpful if I sent an example of the
file? If so let me know how to send it to you

It sounds like you've got Calculations set to Manual instead of automatic.

TOOLS
OPTIONS
CALCULATION Tab
Check "Automatic"
OK

Now Bernard's formula should work, assuming your numbers are stored as
numbers and not text. If you're still getting the wrong results, then here's
another formula you can try:

=IF(ISERROR(FIND("<",A2)),A2,SUBSTITUTE(A2,"<","")/2)

HTH,
Elkar


"Will" wrote:

Using that formula works for the first cell however when i copy the cell and
paste it down the col the values are not different even though the vlues
found int he B col change.. Also the value does not change if the formula
has been copied down the col and then I later make changes to the value found
in the field containing "<0.10"... I would liek to be able to make changes
(update) when the sensor was capable of more accurate readings... for
instance if the sensitivity was actually improved to "<0.050" then the value
i need should be able to be updated on the fly to "0.0025" because thats half
of the sesitivity level.

I hope I am explaining myself well...

"Bernard Liengme" wrote:

If the detected level or the < notation is in B1, use
=IF(ISTEXT(B1),RIGHT(B1,LEN(B1)-1)/2,B1)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Will" wrote in message
...
I have a table the list the "non-Detectable threshold" of some chemical
during an analysis... for instance there is a water sample that is tested
and
the level of Nickel is listed as "<0.10" because that is as sensitive as
the
instrument would allow... some levels are true number such as 1.05.
Inorder
to complete my stat alalysis I need to used the actual reading (ex. 1.05)
when it exists OR i need to use a reading equiv to half of the detectable
about. So for example when the sensitivity was limited to "<0.10" I need
to
use the value "0.05". How can I obtain the "0.05" value and be allowed to
copy it through all the rows???

Do I need to create a pseudo tabel where values entered as text are
changed
to number values...

im so over my head... would it be more helpful if I sent an example of the
file? If so let me know how to send it to you