Convert function gives different results in Excel 2007 and 2013

Hi, welcome to the forum.

I never used 2007, so can't comment on that - all I can tell you is that the 2013 result (which is the same as 2010) is correct. As far as I know, this is hard-coded and can't be changed.

Edit: the correct result comes from international standards from the 1960s or so, so well before Excel.

I can confirm the OP's observation in Excel 2007: =convert(1,"lbm","g") gives 453.592309748811. According to Wikipedia, the correct answer (since the 1960's) should be 453.59237, as given by the 2013 version. As noted, this is hard coded into the function and not the result of any user selectable options. It appears to be an error hard coded into the CONVERT() function in 2007. I would be curious if this error also applies to earlier versions. It has apparently been corrected by 2010. As JonMo indicates, the error is well under 1 ppm (even less than 200 ppb), but it still seems like a careless error.

Take home message, if the gram-pound conversion factor is critical to your application, and you need this to function correctly in 2007, it might be preferable to not use the CONVERT() function.

Originally Posted by MrShorty

I would be curious if this error also applies to earlier versions.

It's in Excel 2002...

2002 - 453.592309748811
2007 - 453.592309748811
2010 - 453.592370000000
2013 - 453.592370000000

I would assume it's also in Excel 2003.

You are the thread starter, so, if you feel the question is resolved, then go ahead and mark it solved.

I personally would be interested if anyone knows the history of this error. Out of curiosity, I put the same function into my old version of Quattro Pro (8), and got the same error. I know that there is a calendar error that got started in early Lotus spreadsheets, and Excel and other spreadsheets perpetuated the error to be compatible with Lotus, which was the dominant spreadsheet in the market at the time. I wonder if this is a similar situation.

From time to time the Système International d'Unités examines the standard units of measurement and makes adjustments to the definitions as measuring technologies become more accurate. I can't find a history of the definitions of the kilogram which might be at play here if the definition has changed.

At one time 1 kilogram was the mass of one cubic decimetre of water. Now it is based upon a platinum-iridium mass.

Good observation, newdoverman. Further research on Wikipedia notes that the 2013 value seems to be based on the international definition agreed on in 1959 (by the US). The 2007 and earlier value is very close to the 1893/94 definition adopted in the US of 2.20462 or 2.20462234 lbm/kg. Still not sure why microsoft and other spreadsheet programmers/developers would choose the older US definition which was superseded in the 1950's and 60's by the international definition. Maybe there is another definition that they were considering, or perhaps they picked a value and figured that the differences among the definitions was small enough to be insignificant, and they did not deem it important enough to choose the 1950's value.

References: mostly here, https://en.wikipedia.org/wiki/Pound_%28mass%29 see the "current use" section and the "historic use -- in the united states" section.

@ MrShorty
Your message #9 reminded me of a period during my working career when the International Standards Organization were reviewing many standards used throughout the world in various areas. Being employed in a scientific research organization this was important to many who worked in the organization. I just wondered if mass was one of the items under review.

An international agreement concerning standards was signed sometime in 1960.

You did a nice bit of research on this. Congratulations.

Yesterday I read, was the 30th anniversary of the start of Excel. This makes me think that the value was taken from other spreadsheets of the time.

Thank you for the feedback.
When you click the star at the bottom left of a message, that is to give reputation points to the person who wrote the message. A popup will appear and it has a field where you can add a comment. A short messeage is sent to the person along with the thread title and your handle. It is a type of appreciation for the help.

The star isn't supposed to do anything if you click the one at the bottom of one of your own messages.

I hope that helps you.