Handling non detects (<0.123) in a database

%3CLINGO-SUB%20id%3D%22lingo-sub-1857542%22%20slang%3D%22en-US%22%3EHandling%20non%20detects%20(%26lt%3B0.123)%20in%20a%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1857542%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20for%20a%20bit%20of%20advice%20and%20hopefully%20a%20simple%20enough%20solution...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20database%20of%20water%20chemistry%20roughly%20130k%20rows%20of%20data%20currently%20but%20this%20will%20grow%20month%20on%20month.%20At%20the%20moment%20I%20run%20multiple%20pivot%20tables%20off%20the%20data%20which%20work%20pretty%20well%20so%20far.%20What%20I%20have%20struggled%20with%20and%20to%20date%20overlooked%20was%20where%20some%20results%20are%20reported%20as%20a%20limit%20of%20detection%20(LOD)%20for%20a%20parameter%20e.g.%20%26lt%3B0.06%20or%20similar.%20Up%20to%20now%20I've%20told%20the%20Pivot%20tables%20to%20ignore%20these%20values%20i.e.%20when%20It%20pulls%20them%20in%20to%20the%20table%20it%20is%20an%20error%20and%20all%20errors%20in%20the%20pivot%20tables%20are%20the%20converted%20to%20%22%3CLOD%3E%3C%2FLOD%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20overall%20goal%20is%20to%20quickly%20review%20and%20present%20several%20tables%20and%20reports%20based%20on%20the%20analysis%20being%20completed%20on%20monthly%20basis%20(this%20is%20not%20fixed%20and%20can%20vary%20from%20a%20few%20hundred%20results%20or%20a%20few%20thousand)%20depending%20on%20the%20no%20of%20samples%20and%20or%20parameters%20being%20tested.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20questions%20based%20on%20this%20are%3A%3C%2FP%3E%3CP%3E1.%20Is%20there%20a%20better%20way%20to%20handle%20these%20%3CLOD%20values%3D%22%22%20in%3D%22%22%20pivot%3D%22%22%20tables%3D%22%22%3E%3C%2FLOD%3E%3C%2FP%3E%3CP%3E2.%20I%20am%20considering%20converting%20the%20%26lt%3B0.06%20to%200.03%20as%20a%20semi%20conservative%20way%20to%20represent%20the%20data.%20this%20may%20be%20acceptable%20as%20I%20will%20not%20be%20saying%20there%20is%20zero.%20More%20conservatively%20I%20could%20convert%20the%20date%20from%20%26lt%3B0.06%20to%200.06%20but%20this%20could%20present%20a%20false%20impression%20of%20the%20results.%20aadvice%20on%20how%20to%20run%20this%20on%20130k%20lines%20of%20data%20easily%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20on%20the%20pivot%20tables%20I%20summarize%20each%20result%20as%20an%20average%20value%26nbsp%3B%20which%20has%20caused%20me%20some%20issues%20also%20but%20has%20worked%20okay%20for%20the%20most%20part%20but%20duplicate%20entries%20have%20caused%20some%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20no%20experience%20of%20any%20coding%20or%20other%20methods%20outside%20of%20the%20normal%20functions%20of%20excel%20so%20I%20would%20likely%20struggle%20if%20I%20need%20to%20introduce%20Macros%20or%20VBA%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1857542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1857696%22%20slang%3D%22en-US%22%3ERe%3A%20Handling%20non%20detects%20(%26lt%3B0.123)%20in%20a%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1857696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F858930%22%20target%3D%22_blank%22%3E%40ultankcc%3C%2FA%3E%26nbsp%3BI%20don't%20understand%20the%20question%2Fproblem%20you%20are%20explaining%20and%20why%20you%20are%20doing%20what%20you%20are%20doing%20and%20such.%26nbsp%3B%20My%20thought%2Fguess%20is%20that%20you%20are%20marking%20those%20values%20%3CLOC%20for%3D%22%22%20easy%3D%22%22%20visual%3D%22%22%20recognition%3D%22%22%20but%3D%22%22%20when%3D%22%22%20you%3D%22%22%20start%3D%22%22%20to%3D%22%22%20do%3D%22%22%20sums%3D%22%22%20and%3D%22%22%20averages%3D%22%22%20you%3D%22%22%20value%3D%22%22%20is%3D%22%22%20off%3D%22%22%20because%3D%22%22%20those%3D%22%22%20small%3D%22%22%20values%3D%22%22%20can%3D%22%22%20add%3D%22%22%20up%3D%22%22%20and%3D%22%22%20make%3D%22%22%20a%3D%22%22%20difference%3D%22%22%3E%3C%2FLOC%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi 

Hoping for a bit of advice and hopefully a simple enough solution...

 

I have a database of water chemistry roughly 130k rows of data currently but this will grow month on month. At the moment I run multiple pivot tables off the data which work pretty well so far. What I have struggled with and to date overlooked was where some results are reported as a limit of detection (LOD) for a parameter e.g. <0.06 or similar. Up to now I've told the Pivot tables to ignore these values i.e. when It pulls them in to the table it is an error and all errors in the pivot tables are the converted to "<LOD" when shown in the table.

 

My overall goal is to quickly review and present several tables and reports based on the analysis being completed on monthly basis (this is not fixed and can vary from a few hundred results or a few thousand) depending on the no of samples and or parameters being tested.

 

My questions based on this are:

1. Is there a better way to handle these <LOD values in pivot tables

2. I am considering converting the <0.06 to 0.03 as a semi conservative way to represent the data. this may be acceptable as I will not be saying there is zero. More conservatively I could convert the date from <0.06 to 0.06 but this could present a false impression of the results. aadvice on how to run this on 130k lines of data easily would be appreciated.

 

Note on the pivot tables I summarize each result as an average value  which has caused me some issues also but has worked okay for the most part but duplicate entries have caused some issues.

 

I've no experience of any coding or other methods outside of the normal functions of excel so I would likely struggle if I need to introduce Macros or VBA

 

Many thanks in advance

 

 

1 Reply
Highlighted

@ultankcc I don't understand the question/problem you are explaining and why you are doing what you are doing and such.  My thought/guess is that you are marking those values <LOC for easy visual recognition but when you start to do sums and averages you value is off because those small values can add up and make a difference?  but that is all a guess.  If I am right, I would recommend leaving the 'real' values and highlight (or 'dim') those cells using conditional formatting.  you can do a simple threshold detect and set the formatting of those small values to be dark gray font on light gray background or something to easily indicate they are <LOC.  If I'm am way off here, maybe you could try again explaining and share a sample sheet.