Hello,

I create my certificates of calibration trough Excel, and I don´t know how can I have for the results a typical scientific notation, for instance 1.23x103, instead of the 1.23E+03 notation.

Can anyone help me ?

Thanks in advance,
Kind Regards,
Hugo

Most people who use Excel (and many other software packages) are familiar with the 1.23E+03 notation, so is it really a problem?

I don't know that there is any easy way to change the notation - at least not automatically. If you type the number yourself is is possible to set the notation to look more standard - you could even put the power of ten as a superscript. You could probably get clever with text functions and find a way to have Excel change the format, but I don't know if it is worth the effort.

Perhaps someone else has a better idea???

Tim F

Most people who use Excel (and many other software packages) are familiar with the 1.23E+03 notation, so is it really a problem?

I don't know that there is any easy way to change the notation - at least not automatically. If you type the number yourself is is possible to set the notation to look more standard - you could even put the power of ten as a superscript. You could probably get clever with text functions and find a way to have Excel change the format, but I don't know if it is worth the effort.

Perhaps someone else has a better idea???

Tim F
I would leave it alone; anyone who's capable of critically evaluating the number should understand scientific notation; the whole idea of scientific notation is to not have to bother with superscript exponents.

My problem is that I got a non-conformity in my last audit because of that.

Hugo

My problem is that I got a non-conformity in my last audit because of that.

Hugo
If you've described a quantity in a way that A) is understood by people who need to understand it and B) wouldn't change if it were expressed in some other form, then your auditor is wrong, unless there's more to this than meets the eye. What was the NC for? What "shall" was violated?

unless there's more to this than meets the eye. What was the NC for? What "shall" was violated?Indeed. Can you tell us more, Hugo?

/Claes

Hello,

I create my certificates of calibration trough Excel, and I don´t know how can I have for the results a typical scientific notation, for instance 1.23x103, instead of the 1.23E+03 notation.

Can anyone help me ?

Thanks in advance,
Kind Regards,
Hugo
Sorry Hugo !!!!
There's no simple way to achieve this in EXCEL. Cientific notation uses X.XXE+xx.
Most bizarre part of your post is getting non-conformity in audit. I'm just curios about it:

Which objective evidence did he/she ask you? what's clause related ?

Best regards.

FL

First - There is no way of getting Excel to do what you ask without rewriting the actual code of the program.

Second - The scientific notation format is the same throughout the world. It is a nationally recognized mathematical representation of a number.

I agree that it is very bizarre (I love that word) that a nonconformance would be issued for this. I am now very curious to know what "shall" was violated by the use of standard scientific notation. Almost sounds to me like your procedure calls for the number to be in the format you described. If this is so, and thats what the nonconformance was for, then just change your procedure to show either method as acceptable. Just a thought.

Good Luck

Hugo,

There is a way in Excel, but you will have to manually do it for each cell. I have no clue how many numbers you have with that kind of notation.

Its pretty trivial

for example you have 1230 for which scientific notation is 1.23 E +03

1. You write down 1.23 X 103
2. Select only the 3 on extreme right
3. Right click on it and chose Format cells
4. Click Superscript

Hope this helps,

Kumar

There are several conventions for writing "scientific notation", depending mostly on what sort of typesetting is available. Consider the number 12,300

The standard Excel format is "1.23E+04".
This is the way Excel outputs the number (if you ask for scientific notation).
You can also type in numbers in this format - simply type "1.23E4" or "1.23E+04". If you want you may add "=" before the number.Standard scientific notation would be "1.23 x 104", where the "4" should be a superscript. (I don't think the software for this forum supports superscripts).
There is no way I know to have Excel convert to exactly this format.
If you type the numbers by hand, you can have Excel put the number in this format - however Excel will not recognize this as the number 12,300.
An alternate format is "1.23 x 10^4", using "^" to indicate a superscript.
With a little cleverness, I forced Excel to convert a number to this format. However the result would no longer be considered a number by Excel, and so no further mathematical operations could be performed.
You can type numbers in this format - "=1.23*10^4" - and have it interpreted correctly by Excel as 12,300. (Note that you must use "*" rather than "x" for multiplication, and you must start with "=" or "+".)IMHO, the second format (using superscripts) is the ideal. That is the way textbooks and technical papers express scientific notation. In a formal report using a word processor, that is how I would do it.

However, superscripts are a luxury that most technical software ignores. For standard reports generated directly from a spreadsheet, the Excel format seems perfectly clear and perfectly standard. Trying to retype or convert that standard formatting would just introduce the potential for more mistakes.


Tim F

I've had to do things similar to this before. What I've done is to use one sheet as an input sheet and another sheet as the output sheet. That way you retain the original input data but have it nicely formatted for the report.

Here's a quick macro I did to transform the data into the scientific notation you wanted:


Private Sub Worksheet_Change(ByVal Target As Range)

' example using C2 as entry cell
If Target.AddressLocal(False, False) <> "C2" Then Exit Sub

Dim coefficient As Double
Dim exponent As Integer
Dim sciNot As String
Dim lenExp As Integer
Dim lenSciNot As Integer
Dim ws As Worksheet

coefficient = CDbl(Target.Value)
exponent = 0

Do While coefficient / 10 > 1
coefficient = coefficient / 10
exponent = exponent + 1
Loop

sciNot = Format(coefficient, "#.00") & " x 10" & exponent
lenSciNot = Len(sciNot)
lenExp = Len(CStr(exponent))

Set ws = Application.Sheets("Sheet2")
ws.Range("C2").Value = Format(coefficient, "#.00") & " x 10" & exponent
ws.Range("C2").Characters(lenSciNot - lenExp + 1, lenExp).Font.Superscript = True

Set ws = Nothing

End Sub


The result is output to Sheet2, cell C2 in this case, while the input (on Sheet1 in my case) retained its format.

HTH,

Nick

A nice solution :agree1: and once again we see the power of the Cove, but in this case it seems like a cure looking for a disease. I'm still interested in knowing what the auditor's gripe was.

... in this case it seems like a cure looking for a disease. I'm still interested in knowing what the auditor's gripe was.

I agree wholeheartedly (I just think it's fun writing little macros).

Thank you a lot Nick !!!
This macro is a great help !

Hugo Morais

Thank you JSW05, Tim, Claes, Flavio, Justin, Kumar and Nick for you help !

I´have not being at the office but I know that the clause referred for the non-conformity was the 5.10 (presentation of results I think). Next week I tell you exactly what is written in the audit report. I remember that the auditor told me that this notation XXE+XX was not clear enough.

Once again, thank you for your good advices.

Hugo Morais

The non-conformity was:
"The scientific notation used by the lab for the presentation of uncertainty is not correct. clause:5.10".

Hugo

The non-conformity was:
"The scientific notation used by the lab for the presentation of uncertainty is not correct. clause:5.10".

Hugo

That's not much of an explanation; the auditor should be asked to provide more detail in writing. I'm no expert on 17025 (and I assume that's the standard in question) but I'm guessing that perhaps the auditor is looking for some form of standard notation that I'm not familiar with. There is an ISO document called Guide to the Expression of Uncertainty in Measurement (http://www.iso.org/iso/en/prods-services/otherpubs/Measurements.PublicationList?CLASSIFICATION=MEASUREMENTS) (scroll down the page) than might shed some light.

Nick,

That's a slick little macro. I did just notice that it only works for numbers greater than 1. So if you type in 0.023 it will fail. Or -1234 would fail.

Perhaps you could change the code from

exponent = 0

Do While coefficient / 10 > 1
coefficient = coefficient / 10
exponent = exponent + 1
Loop

To something like

exponent = int(log(abs(coefficient)))
coefficient = coefficient / 10^exponent

I think that sould work for any value of the input. (I'm not big into macros, so the coding may not be exactly right).


Tim F

Nick,
Perhaps you could change the code ...


Good catch, Tim, but as they say, "the rest is left as an exercise for the reader." :)

Trust me, I can get sucked into solving other people's problems very easily. They're always more fun to work on than your own. (Why is that?) But, unfortunately, I'm knee deep in my own right now and just spending a few minutes on The Cove is an indulgence.

Nick,
Perhaps you could change the code ...


Good catch, Tim, but as they say, "the rest is left as an exercise for the reader." :)

Trust me, I can get sucked into solving other people's problems very easily. They're always more fun to work on than your own. (Why is that?) But, unfortunately, I'm knee deep in my own right now and just spending a few minutes on The Cove is an indulgence.

Yes, you´re right, I have to have check the GUM...thank you.

I've had to do things similar to this before. What I've done is to use one sheet as an input sheet and another sheet as the output sheet. That way you retain the original input data but have it nicely formatted for the report.

Here's a quick macro I did to transform the data into the scientific notation you wanted...

This is really cool! Tim's adjustment is good also! And with a little tweaking (like incrementing the exponent by 3 and dividing the mantissa by 1000) it could even format reports in Engineering format - something Excel has always lacked. (But the exponent would have to start at 0 or a multiple of 3.)

Next on the wish list for developers - make that a native part of Excel, or even better put it into the Calc application of OpenOffice.org and leave Micro$oft even further behind. And make it a number instead of text.

Thank you, Nick! :applause:

Thank you JSW05, Tim, Claes, Flavio, Justin, Kumar and Nick for you help !

I´have not being at the office but I know that the clause referred for the non-conformity was the 5.10 (presentation of results I think). Next week I tell you exactly what is written in the audit report. I remember that the auditor told me that this notation XXE+XX was not clear enough.

Once again, thank you for your good advices.

Hugo Morais

Hmmm ... the text of 5.10.1 says the results "shall be reported accurately, clearly, unambiguously and objectively, and in accordance with any specific instructions in the test or calibration methods."

The format "n.nnnE+nn" has been standard in spreadsheet software since the late 1970's, because I remember it on the first spreadsheet application I used. (But I can't remember the name of the application! Can anyone help? It was before Lotus 1-2-3 and it ran on TRS-80 and CP/M machines.) It was also the exponential format used in the FORTRAN 77 (from 1977) programming language used extensively on mainframe computers of the time for scientific, engineering and technical work. It is NOT new or unusual. To anyone who is competent to read the reports (that is, anyone with education and/or experience in science, engineering or any technical work), it is as easy to understand as the typeset "n.nnn x 10^nn" (understanding that the "^nn" is really a superscripted number.) It is accurate, it is clear, and it is unambiguous. Used properly, it reports all significant figures without extraneous (and possibly confusing) strings of zeroes. It is also (thanks to Microsoft) a de facto global standard. The only possible way I could see a NC would be if the procedure being used at the time instructed the user to report the data in some different format.

The format "n.nnnE+nn" has been standard in spreadsheet software since the late 1970's, because I remember it on the first spreadsheet application I used. (But I can't remember the name of the application! Can anyone help? It was before Lotus 1-2-3 and it ran on TRS-80 and CP/M machines.)

VisiCalc (http://en.wikipedia.org/wiki/VisiCalcVisiCalc)

The only possible way I could see a NC would be if the procedure being used at the time instructed the user to report the data in some different format.

Exactly.:agree1:

THANKS FOR THE MEMORY JOG! Yes, it was VisiCalc.

Note - I also added FORTRAN, which I remembered about 0.3 ms after clicking the [Submit Reply] button!

GCP

Tried for a while to get cells formatted into commonly utilized engineering eponetiation (factors of 3, i.e. 3, 6, 9, 12 ,etcetera). :mad:

FINALLY figured it out:

Utilize: ###.000E+00

Hope this helps another!!!

:biglaugh:

Utilize: ###.000E+00 :biglaugh:
Wow! What a simple method. :agree1:

After all the efforts to create a macro, it turns out that Excel does have a built-in way to do engineering notation. I tried it and it works like a charm.


Tim F

Ha! That's great. Beats the pants off of my fancy-smancy macro.

I played with it and found you can add a space between the last decimal and E ("###.000 E+00") as well.