need help: spreadsheet to convert Phosphorus - Phosphate W/ the Hanna margin of error

[smartwater101]

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?

 

[bertoni]

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.

 

[RobZilla04]

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

 

[homer1475]

EDIt:
NVM I understand what your trying to do now.

 

[smartwater101]

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.

That sounds like the right track but I believe it's 5 ppb AND 5% (not either/or). So I probably need to sneak a little more math in there.

 

[bertoni]

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.

 

[RobZilla04]

Sorry for the confusion, I'm new to the ULR as well.

 

[smartwater101]

I figured it out!!

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! :dance:

 

[smartwater101]

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 )

 

[RobZilla04]

Very cool, thx for sharing.

 

[smartwater101]

Very cool, thx for sharing.

No problem! Hopefully it will be of use for some random Googler in the future.

 

[Daddyrawg]

No problem! Hopefully it will be of use for some random Googler in the future.

YUP like myself.. Thanks BUD:beer: