Monday, November 22, 2010

Conductivity to Salinity Conversion for Excel

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

21 comments:

Kevin Hardin said...

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)

James Douglass said...

Ah, good tip, Kevin. That's just for looks though, right? Once you put in conductivity and temperature data everything should make sense?

Ryan Kempster said...

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

James Douglass said...

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.

RachelWhite said...

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!

James Douglass said...

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

David said...

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

Scuttlebutte said...

James,

the link to the Excel file is not working for me, but this is something I would really appreciate having.

heidi f said...

Hi James,
This is just what I need to convert my Specific Conductivities to Salinities for our YSI measurements.I wanted to check with you on the units. Is your Specific Conductivity in your Excel spreadsheet expecting mS/cm?

Troy said...
This comment has been removed by the author.
Steve and Becky West said...

James,

Nice spreadsheet, it seems to match other websites. Is your "Measured Conductivity" temperature compensated (has ATC) or is the conductivity uncompensated and the salinity calculations take care of that?

Thanks,
Steve

uday said...

Dear Prof. Douglass,
This conductivity to salinity converting spreadsheet is awesome. It helped me a lot. Thanking you.

Uday Bhan Singh
Research Scholar (India)
email: ubsday@gmail.com

uday said...

Dear Prof. Douglass,
This conductivity to salinity converting spreadsheet is awesome. It helped me a lot. Thanking you.

Uday Bhan Singh
Research Scholar (India)
email: ubsday@gmail.com

kusaram yadhavan said...

Hi, probably our entry may be off topic but anyways, I have been surfing around your blog and it looks very professional. It’s obvious you know your topic and you

appear fervent about it. I’m developing a fresh blog plus I’m struggling to make it look good, as well as offer the best quality content. I have learned much at your

web site and also I anticipate alot more articles and will be coming back soon. Thanks you.







Data Conversion

Dana Latayfeh said...

Dear James,
I have some problems in converting conductivity to salinity can you answer me about this
-My conductivity meter measure it in Ms only not Ms/cm so its the same?
- also the maximum conductivity in my system (drinking water) is 600 Ms can I use your sheet at this values?

Faten Horriche said...

Hi James,
I'm using the same equation to caculate Salinity, but my prolem is : how can we calculate the Salinity if it is less than 2 ???
Thanks
Faten

Jacque Cresswell said...

Hi James,

This looks great, but I'm having trouble downloading the excel sheet. Is there something we can work out so I may have access to it? Also, the link for the 1983 paper you referenced is not working. What paper is that?

Thanks for your help!

James Douglass said...

Hi Jacque- I updated the links to the original paper and to the downloadable file. Please let me know if they work for you. Thanks! -James

Jean Francois Rault said...

Hi James,

I have download your Excel spreadsheet and it is very useful! Thx
What is the relation between salinity unit in "psu" and salinity unit in "mg/l"?
Is it x 1000?

JF

James Douglass said...

Hi JF- PSU is a close (but not exact) approximation of PPT (g/liter). 1000 x PSU should be a pretty good approximation of mg/l.

Michelle Groncki said...

Hi James, I had a quick question regarding the reference conductivity in your spreadsheet. I have a meter that reads conductivity, temperature, and TDS that is used and it we can use a couple different conductivity standards to calibrate it. Now, is the mentioned reference conductivity the same as a conductivity standard? I'm trying to figure out where that comes from and how I get that value for the calculations.