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%3CLINGO-SUB%20id%3D%22lingo-sub-1951976%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-1951976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bthanks%20for%20the%20response%20and%20sorry%20for%20delay%20in%20my%20response%20as%20well%20as%20not%20making%20myself%20clear.%20My%20source%20data%20contains%20values%26nbsp%3B%20the%20less%20than%20symbols%20e.g.%22%26lt%3B0.01%22%20so%20when%20I%20sum%20or%20average%20values%20this%20data%20it%20results%20in%20an%20error%20on%20the%20pivot%20table.%20I%20was%20hoping%20that%20there%20is%20a%20way%20to%20avoid%20these%20calculation%20errors.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1953436%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-1953436%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%3Bso%20in%20your%20op%20you%20mention%20a%20couple%20of%20options%20that%20you%20were%20looking%20at%20doing%20(replacing%20%22%3CXXX%3E%3C%2FXXX%3E%3C%2FP%3E%3CP%3EAre%20you%20looking%20for%20advice%20on%20which%20way%20is%20best%20w%2Fr%20to%20data%20analysis%2C%20or%20how%20to%20accomplish%20this%20replacement%2C%20or%20for%20alternative%20solutions%3F%3C%2FP%3E%3CP%3EWith%20respect%20to%20the%20pivot%20table%2C%20there%20are%20ways%20to%20create%20custom%20data%20columns%20in%20a%20pivot%20table%20but%20are%20you%20only%20using%20the%20pivot%20table%20to%20calculate%20your%20averages%3F%20If%20so%20and%20you%20are%20just%20reporting%20that%20average%2C%20I%20could%20imagine%20a%20formula%20to%20calculate%20the%20average%20without%20the%20pivot%20table.%26nbsp%3B%20I'm%20just%20trying%20to%20understand%20the%20direction%20to%20go.%3C%2FP%3E%3CP%3EYou%20also%20mention%20a%20problem%20with%20duplicate%20values%20being%20a%20problem.%26nbsp%3B%20What%20exactly%20do%20you%20mean%20by%20this%20because%20either%20direction%20can%20be%20plagued%20by%20this%20sort%20of%20problem%20and%20it%20would%20be%20helpful%20to%20understand%20this%20problem%20before%20working%20on%20a%20solution%20for%20you%20only%20to%20have%20this%20problem%20need%20to%20get%20addressed%20later.%3C%2FP%3E%3CP%3EAs%20for%20the%20data%20analysis%20view%20point%2C%20maybe%20doing%20it%20both%20or%20all%203%20way%20to%20give%20a%20min-max%20bracket%20around%20the%20average%20to%20give%20a%20confidence%20interval.%26nbsp%3B%20(i.e.%20assume%20%3CLOC%20min%3D%220%2C%22%20avg%3D%22LOC%2F2%2C%22%20max%3D%22LOC).%26nbsp%3B%22%20it%3D%22%22%3E%3C%2FLOC%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

 

3 Replies

@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. 

@mtarler thanks for the response and sorry for delay in my response as well as not making myself clear. My source data contains values  the less than symbols e.g."<0.01" so when I sum or average values this data it results in an error on the pivot table. I was hoping that there is a way to avoid these calculation errors.

@ultankcc so in your op you mention a couple of options that you were looking at doing (replacing "<xxx" with a value =xxx/2 or =xxx) and you mention how you have a pivot table that is replacing them with "<LOC".   So I have a number of questions. 

Are you looking for advice on which way is best w/r to data analysis, or how to accomplish this replacement, or for alternative solutions?

With respect to the pivot table, there are ways to create custom data columns in a pivot table but are you only using the pivot table to calculate your averages? If so and you are just reporting that average, I could imagine a formula to calculate the average without the pivot table.  I'm just trying to understand the direction to go.

You also mention a problem with duplicate values being a problem.  What exactly do you mean by this because either direction can be plagued by this sort of problem and it would be helpful to understand this problem before working on a solution for you only to have this problem need to get addressed later.

As for the data analysis view point, maybe doing it both or all 3 way to give a min-max bracket around the average to give a confidence interval.  (i.e. assume <LOC min=0, avg=LOC/2, max=LOC).  It's a bit more work but if it is critical to know the tolerance of your value that might be best.