At work the other day I was trying to make sense of a bunch of water quality sensor data from the Indian River Lagoon in Florida. The sensor had measured electrical conductivity as a proxy for salinity (saltier water conducts electricity better because the salt ions ferry the charges), but no conversion had been made from conductivity to actual salinity.
It turns out that converting from conductivity to salinity is tricky, because the mathematical relationship depends in a complicated way on both temperature and pressure. (Although you don't have to worry about the pressure part if you're dealing with surface waters at 1 atm pressure.) In my Google search for answers I found a website that will do the conversion for you if you enter a particular conductivity and temperature into a box. But that's not much use if you have hundreds or thousands of concurrent temperature and conductivity recordings that you need to convert in a spreadsheet. So I dug deeper and found a 1983 paper that had the actual formulas needed for the conversion. I put the formulas into a Microsoft Excel worksheet and fed them the Indian River Lagoon sensor data. It worked! I think.
Interestingly, it seems that the part of the Indian River Lagoon where the readings were taken gets a bit saltier than the ocean in Florida's winter dry season, but is only about 2/3 as salty as the ocean in the summer wet season.
Anyway, I'm making the Excel file that does the conversion available for download here, along with an example using the sensor data from the IRL. I'm trying to embed it below. If you're a scientist and you notice some error in my calculations, let me know ASAP so I can fix it. Thanks.
East Point: Past, Present, and Future (?)
1 hour ago
7 comments:
What if you notice an error but aren't a scientist?
To clean my interface I often put in an If statement to avoid divide by zero conditions.
For example
=if(F29=0,"",(0.7*$H$2)/F29)
Ah, good tip, Kevin. That's just for looks though, right? Once you put in conductivity and temperature data everything should make sense?
Dear James, I just came across your excel spreadsheet that automatically converts conductivity to salinity, which is great. But I was wondering if you have a version that can convert salinity to conductivity? I have tried to rearrange the equations but sadly my math skills are not too great. Alternatively do you know any website that has the excel formulas already done?
cheers
Ryan
Hey Ryan- I don't have that, because it's a more complicated calculation that you would have to do iteratively, and I'm not that smart or computer savvy. If you want to attempt to do it yourself you can google the UNESCO 1983 paper and get the formulas. If you only need to find one conductivity value you can fill in the temperature box on my spreadsheet and manually adjust the conductivity until you get the right salinity.
Dear James,
I have a question about the example conversion worksheet in your excel file - is the Reference Conductivity column's values supposed to increase within the column? I'm assuming it's supposed to remain 42900 for all measurements. I was also wondering, if EC measurements were taken after using calibration solutions for 25C, could this table still be used if the Reference Conductivity is in 35psu and 15C?
Thanks for the help!
Hi Rachel,
Yes, the reference conductivity should remain the same. Selecting the green cells and clicking and dragging the lower right hand corner down to fill the lower rows may not work in your web browser, but it should work if you download the file into Excel. I don't know the answer to the second part of your question, unfortunately, but if you figure it out, let me know.
-James
Tested your excel sheet with NOAA PORTS data and worked fine:
go here and download current temperature data - be sure to use celcius:
http://www.co-ops.nos.noaa.gov/data_menu.shtml?stn=8537121%20Ship%20John%20Shoal,%20NJ&type=Meteorological+Observations
note dates, then go here and download current conductivity data - be sure to set dates the same as used above:
http://www.co-ops.nos.noaa.gov/data_menu.shtml?stn=8537121%20Ship%20John%20Shoal,%20NJ&type=Meteorological+Observations
Enter data into your spreadsheet, then plot salinity over time and compare with salinity data at bottom of page here
http://www.co-ops.nos.noaa.gov/ofs/ofs_station.shtml?stname=Ship%20John%20Shoal&ofs=db&stnid=8537121
The graphs should look the same
Post a Comment