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! 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 made the Excel file that does the conversion available for download below, along with an example using the sensor data from the IRL. If you're a scientist and you notice some error in my calculations, let me know ASAP so I can fix it. Thanks.

2021 UPDATE: Readers' comments directed my attention to a 1986 paper (Hill et al., The Extension of the Practical Salinity Scale 1978 to Low Salinities) that allows the calculator to work in the range from 0 to 2 psu. I incorporated those corrections into the spreadsheet, which should now be reliable in those salinity ranges. I hope this will be helpful for those working in very diluted seawater environments like oligohaline estuaries.

32 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?

Unknown 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

Anonymous 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?

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

Unknown 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?

Unknown 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

Unknown 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

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

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

Unknown said...

Hi,
thank you for creating this it is very useful.
i noticed though that your reference conductivity on the example sheet starts as 42900 and then goes up 1 (42901, 42902, etc.) for each line going down.
i believe this is unintentional and happened when you filled in the cells by dragging down? it should be the same value 42900 throughout right?
greetings dominik

Unknown said...

Hi James! Hope your spreadsheet helps a lot to calculate the practical salinity. There is always some confusion with units and standards of conductivity measurements, even with the different definitions and units of conductivity, rooted from the different conductivity sensors' manufacturers and different method of determination of salinity in different fields of aquatic science. The Practical Salinity Scale-1978, some equations of which you have used, serves mainly Oceanography, where request of accuracy is quite high (in practical salinity +/-0.003). But envinromental study can be satisfied by simple conversion of the temperature corrected specific conductance to TDS. In this case, in your speadsheed conductivity input shouldn't be corrected to 25degC and standard conductivity value precisely should be 42914 uS/cm.
A few years ago I made my online calculators of practical salinity with pressure inputs and also calculators of salinity for salinometers. In addition, I have a convertor from the practical salinity to conductivity, in a case if needs to calibrate CTD with salinity samples. Plus a lot of other stuff about salinometry:) Enjoy: www.salinometry.com

roberta r said...

Hi James!
I am a biomedical engineer and I work as a research fellow. I downloaded and used your spreadsheet to process data obtained with a commercial environmental sensor. Your spreadsheet is the best I found! really useful! I have a question: what is the uncertainty on salinity measurement? Clearly I refer only to uncertainty due to the calculation algorithm.
greetings Roberta

roberta r said...

Hi James!
I am a biomedical engineer and I work as a research fellow. I downloaded and used your spreadsheet to process data obtained with a commercial environmental sensor. Your spreadsheet is the best I found! really useful! I have a question: what is the uncertainty on salinity measurement? Clearly I refer only to uncertainty due to the calculation algorithm.
greetings Roberta

James Douglass said...

Hi Roberta- Thanks! I'm not sure without looking back at the original literature what the "uncertainty" of the conversion is, but I think the fit of the conversion is quite close within the normal range of salinity and temperature encountered in the environment.

Unknown said...

Hi James,
This spreadsheet is great!
I wanted to check what unit of conductivity should go in the 'Measured Conductivity' column?
I was wondering if the equations are for salinity ranging 2 < SP < 42?
If so, do you know how to incorporate the lower salinities? I found the following paper however, I am not sure how to use such formulas..

'The extension of the Practical Salinity Scale 1978 to low salinities'

Any help would be much appreciated.

Thanks,

Louise

Unknown said...

Looks like your reference conductivity is changing in your example. This might have been a pull down error in excel.

Fitriani Khasanah said...

Hi James,

I tried to download the Excell file calculation, but the link is broken. Can you please reupload the file or can you please send me the file to my email fitriani.khas@gmail.com.

Thanks,
Ani

GeoGebrain said...

Hi James.

Could you please send me this file - the link isn't working now.

Many thanks,

Neil

Unknown said...

Hi James,

I am also having trouble downloading this file - can someone post a new link in the comments? Or if you could email it to me it would be greatly appreciated!

-Stephanie

James Douglass said...

Hi All- Thank you for your comments on this post. I've fixed the download link (I think) and have also updated the calculator to include the Hill et al. 1986 correction for low salinities. My next goal will be to get this hosted on my employer (Florida Gulf Coast University)'s website in way that makes it a little more official and easier to cite in reports and publications.

William Aley said...

Thank you for your time and input James. I found your spreadsheet while searching for conductivity to salinity conversions for groundwater measurements near the St. Lucie Estuary in Stuart, Fl. I understand the formulas you provided are for surface water at 1 atmospheric pressure. I have found some algorithms for conversion that include the pressure factor but they are WAY beyond my current mathematical prowess. The groundwater that I am measuring is all from less than 30' below ground surface. I'm assuming its safe to assume values from your spreadsheet will return relatively accurate values for ground water in the upper 10-20' of the aquifer? Any input on that would be appreciated. Thanks again!

James Douglass said...

Hi William- The formulas involving pressure are probably beyond my mathematical prowess, too. Is the sensor you're getting readings from 30' below the surface, or are you pumping water up from that depth and measuring it at the surface? You could maybe do a little test for yourself to see how strong the pressure effect is if you're able to get a sample at depth, but also sip some water up from the that depth and measure the same water at the surface.

William Aley said...

Hi James - Thanks for following up. I continue to use your spreadsheet for conversion of shallow groundwater conductivity to salinity and it is working great! The groundwater that I am measuring is all 30' depth and less. It turns out that pressure is not a concern in 99% of cases. Since my last message to you I have worked with the conductivity sensor manufacturer to work out the math for depth corrections and it turns out that for looking at salinity on the ppt range for typical purposes groundwater depth has negligible effects on accuracy of the conversion. The math indicates that it may come into play when looking at conversions of data from 1000'+ but even then only at tenths of a ppt or so. Again, thanks for following up. Cheers.