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

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:

note dates, then go here and download current conductivity data - be sure to set dates the same as used above:

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?

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

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

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,

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.

Dominik Kneer 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

Humidity Meter said...

Nice blog very informative...
Thanks for sharing...
Conductvity meter, Humidity Meter, Controller, pH countroller

Unknown said...
This comment has been removed by the author.
Igor Shkvorets 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

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