|
05/21/2018, 06:17 PM | #1 |
Registered Member
Join Date: Aug 2012
Location: Los Angeles
Posts: 382
|
need help: spreadsheet to convert Phosphorus - Phosphate W/ the Hanna margin of error
This is both a math and a Google Spreadsheet question: I want to convert Phosphorus to Phosphate....
the math to convert is easy enough. 12ppb phosphorus reading would = 12x3.066 = 36.792 part per billion phosphate divide by 1000 = 0.037ppm phosphate wich I have set up already https://i.imgur.com/bMOVRjE.png BUT The Hanna checker has an accuracy range of ±5 ppb ±5% of reading And this is where my spreadsheet skills fall apart. Can anyone help?
__________________
160 mixed reef: Hippo tang, Foxface, clownfish, 25+ nano gobies/blennies/cardinals |
05/21/2018, 11:44 PM | #2 |
RC Mod
Join Date: Mar 2002
Location: Mountain View, CA, USA
Posts: 88,616
|
Well, I can't even do that much, but if you don't find an answer, let me know, and I'll try to do some research.
__________________
Jonathan Bertoni |
05/22/2018, 04:55 AM | #3 |
Registered Member
Join Date: Sep 2016
Location: St. Augustine, FL
Posts: 1,857
|
You need to account for the range in the ppb prior to the math converting to ppm.
Thus 12 = range of 7-17. Use an additional two lines, one (B4) for B3 +5ppb(or 5%) and one (B5) for B3 -5ppb(or 5%). Then run the same formula for each of those to convert to ppm. At least that is my simpleton understanding.....
__________________
90g Mixed Reef |
05/22/2018, 05:31 AM | #4 |
Registered Member
Join Date: Mar 2015
Location: Upstate NY
Posts: 5,313
|
EDIt:
NVM I understand what your trying to do now.
__________________
80G SCA Build: http://reefcentral.com/forums/showthread.php?t=2560256 Originally posted by der_wille_zur_macht: "He's just taking his lunch to work" |
05/22/2018, 10:07 AM | #5 | |
Registered Member
Join Date: Aug 2012
Location: Los Angeles
Posts: 382
|
Quote:
__________________
160 mixed reef: Hippo tang, Foxface, clownfish, 25+ nano gobies/blennies/cardinals |
|
05/22/2018, 11:21 AM | #6 |
RC Mod
Join Date: Mar 2002
Location: Mountain View, CA, USA
Posts: 88,616
|
It is 5% and 5 ppb, unfortunately. So you need to take the measurement, subtract 5 and 5% from it to compute the low. The high would be adding 5 ppb ad 5%. You'd probably want to clamp the low at zero, and avoid showing negative numbers.
__________________
Jonathan Bertoni |
05/22/2018, 11:23 AM | #7 |
Registered Member
Join Date: Sep 2016
Location: St. Augustine, FL
Posts: 1,857
|
Sorry for the confusion, I'm new to the ULR as well.
__________________
90g Mixed Reef |
05/22/2018, 01:10 PM | #8 |
Registered Member
Join Date: Aug 2012
Location: Los Angeles
Posts: 382
|
I figured it out!!
Okay so I got it working. In case you're wonder how to set it up: Cell B4: Enter your reading from the test Cell C4: =B4*3.066/1000 Cell D4: =ROUND(((B4-5)*3.066/1000)*0.95,3)&" - "&ROUND(((B4+5)*3.066/1000)*1.05,3) Thats it!
__________________
160 mixed reef: Hippo tang, Foxface, clownfish, 25+ nano gobies/blennies/cardinals |
05/22/2018, 01:25 PM | #9 |
Registered Member
Join Date: Aug 2012
Location: Los Angeles
Posts: 382
|
An alternative formula: This will keep the reading out of the negative so no going below zero
Cell D4: =MAX(ROUND(((B4-5)*3.066/1000)*0.95,3) , 0 )&" - "&MAX(ROUND(((B4+5)*3.066/1000)*1.05,3) , 0 )
__________________
160 mixed reef: Hippo tang, Foxface, clownfish, 25+ nano gobies/blennies/cardinals |
05/23/2018, 04:28 AM | #10 |
Registered Member
Join Date: Sep 2016
Location: St. Augustine, FL
Posts: 1,857
|
Very cool, thx for sharing.
__________________
90g Mixed Reef |
06/01/2018, 12:37 PM | #11 |
Registered Member
Join Date: Aug 2012
Location: Los Angeles
Posts: 382
|
No problem! Hopefully it will be of use for some random Googler in the future.
__________________
160 mixed reef: Hippo tang, Foxface, clownfish, 25+ nano gobies/blennies/cardinals |
10/17/2018, 03:22 PM | #12 |
Registered Member
Join Date: May 2018
Posts: 814
|
|
Thread Tools | |
|
|