Hello forum, first post so I hope what I am trying to accomplish makes sense once I explain it.
In the attached file is a mass of lab data. What I am doing is taking the spreadsheet that the lab gave me and cleaning it up to run through the risk assessment program PRO UCL.
To get it 'finished', all non-numerical characters must be removed from the sample results. The qualifier "<" is used for samples that were below the detection limit. I can count them as 'detects' at half of the detection limit, which is needed for determining cumlulative risk as often only one or two samples are above the detection limit (can't run statistics on one sample).
Also, detects are bold, and shaded when above regulatory standard.
So what I need to do is this-
(create new sheet and copy over data)
1)leave detects as is
2)remove "<" from non-detects
3)calculate half of the detection limit (for ND's) to make the result a 'detect'
I have tried the find/replace method, however it gets cumbersome when trying to go back and calculate half the detection limit. I also attempted creating a macro for this, however that was beyond my skill level.
Any and all help is apreciated, I'll try to answer questions if you have any.
Bookmarks