chbs 20090119 10:35 Posting: # 3060 Views: 16,748 

Dear All, We are using the excel sheet for the calculation of mean, SD and CV during the bioanalytical analysis. If we compare the same calculation with validated WinNonlin Software, then whether it can serve as supporting data for the excel sheet usage and can we say that the excel sheet is validated because of this comparision. Or it requires complete validation of the excel sheet? Thanks in advance chbs 
martin Senior Austria, 20090119 11:52 @ chbs Posting: # 3062 Views: 14,970 

dear chbs ! never never never never use excel for any statistical calculations: spreadsheet addiction best regards martin 
Ohlbe Hero France, 20090119 23:54 @ martin Posting: # 3067 Views: 14,748 

Dear martin and chbs, » never never never never use excel for any statistical calculations Use of Excel has been previously discussed on the forum, for instance here. Even though it is certainly not the correct tool to use for complex (or even intermediate) stats, I would not be shocked to see it used for descriptive stats (mean, SD, CV), where it is easier to use than a stats package for the basic user (using SAS or R to calculate the mean of 6 values would be like using a sledgehammer to kill a mosquito, and would seriously reduce the number of possible users). However this requires some precautions: validate your spreadsheet by comparing the results with those obtained with another software (or even manually), and then lock and passwordprotect your spreadsheet, only leaving unlocked the cells in which you will enter your data. Upgrade to a new version of Excel (or any version change) will require a revalidation. Regards Ohlbe 
martin Senior Austria, 20090120 14:52 (edited by martin on 20090120 15:12) @ Ohlbe Posting: # 3075 Views: 15,287 

Never never never never use Excel. Not even for calculation of arithmetic means: http://www.cs.uiowa.edu/~jcryer/JSMTalk2001.pdf http://pages.stern.nyu.edu/~jsimonof/classes/1305/pdf/excelreg.pdf http://www.agresearch.co.nz/Science/Statistics/exceluse1.htm best regards martin Edit: Capital letters reformatted to bold. Please see here. [Helmut] 
Helmut Hero Vienna, Austria, 20090120 17:03 @ martin Posting: # 3076 Views: 14,746 

Hi Martin, I really loved one of the examples from your last reference: STDEV(1,2,3) = 1 which looks even more funny if you run this set (in M$Excel 5.0 and 2000 – the only versions I have handy): STDEV(0.90000001,0.90000002,0.90000003) = 0 R 2.8.1 sd(c(90000001,90000002,90000003)) = 1 STATISTICA 5.1H St.Dev.(90000001,90000002,90000003) = 1 NCSS 2001 Standard Deviation (90000001,90000002,90000003) = 1 Maxima 5.15.0* std1([90000001,90000002,90000003]); = 1 WinNonlin 5.2.1 STDEV(90000001;90000002;90000003) = 0 As you said, it’s advisable to use statistical software for statistical calculations (surprisingly PK software failed this simple test as well). Since this thread is in the Bioanalytics category: as far as I know Thermo’s Xcalibur software has only the options to store data in its own proprietary format or to export xlsfiles. But help is on the way. All (?) statistical software are able to import xlsfiles.
— All the best, Helmut Schütz The quality of responses received is directly proportional to the quality of the question asked. ☼ Science Quotes 
d_labes Hero Berlin, Germany, 20090121 11:00 @ Helmut Posting: # 3083 Views: 15,792 

Hi Helmut, hi Martin, flame warin' a little bit with you. Never, never, never use any software! Never, never, never trust any computer! You can always find numbers which cannot handled by your preferred piece of software on your preferred machine (Remember the Pentium FDIV bug). Therefore soft and hardware are not trustworthy and useless in a general sense . By the way, M$Excel is an extraordinary software in this way. — Regards, Detlew 
Helmut Hero Vienna, Austria, 20090121 13:00 @ d_labes Posting: # 3091 Views: 14,550 

Dear DLabes! » flame warin' a little bit with you. Yeah, I like that! » By the way, M$Excel is an extraordinary software in this way. Definitely. If one has to use it for any reasons, he/she should follow the Ohlbe's suggestions. BTW, even in M$Excel it's possible to get the correct answer (if STDEV() is avoided):  A  B gives  A  B There are even better speadsheets around. OpenOffice Calc returns 1 if STDEV() is used, even with the oldest version I have on one of my machines, namely v2.0 from 2005.— All the best, Helmut Schütz The quality of responses received is directly proportional to the quality of the question asked. ☼ Science Quotes 
Ohlbe Hero France, 20090121 14:03 (edited by Ohlbe on 20090121 23:17) @ Helmut Posting: # 3098 Views: 14,448 

Dear HS, Amazing. I fail to reproduce the error in SD calculation in the example you give, using the French version of Excel 2003 I have here. I get the correct value of 1. I'll check with another version of Excel (also French) at home tonight. Edit: error reproduced at home with the French version of Excel 2000... Shit ! Does Microsoft also call this a "feature", rather than a bug ? And considering the fact that the bug appears with some sets of data, but not others, validating a spreadsheet by comparison with another software or manual calculation would not be sufficient. Regards Ohlbe 
d_labes Hero Berlin, Germany, 20090121 15:42 (edited by d_labes on 20090121 16:06) @ Ohlbe Posting: # 3099 Views: 14,440 

Dear HS, dear Ohlbe, seems we are going to validate M$ Excel, which we will never, never, never use for any statistics? Amazing! To add my two coins: Excel 2003 SP3 (german), Windows XP professional on a DELL Pentium 4 (3.0 GHz) machine with 1 GB RAM, chip set graphics .........  A gives  A For me the result in WinNonlin in Helmut's post is more interesting! Seems they (far side) have validated it against Helmut's Excel version. That adds much credit to the trustworthyness in WinNonlin results of iterative REML analyses of replicate crossover designs with factor analytic decomposition of the between covariance matrix. Really! — Regards, Detlew 
Helmut Hero Vienna, Austria, 20090122 02:49 @ Ohlbe Posting: # 3101 Views: 14,477 

Dear Ohlbe, » Does Microsoft also call this a "feature", rather than a bug ? If you boil this page down, bug. Although they don’t confess that pre2003 was buggy – they use some kind of Newspeak. They give also this formula SQRT((SUMSQ(values)(SUM(values)^2)/COUNT(values))/(COUNT(values)1)) as ‘at least a good approximation to the value of STDEV as found by earlier versions of Excel.’There was a major change between v2003 and its predecessors. See this overview in Microsoft’s Knowledge Base. — All the best, Helmut Schütz The quality of responses received is directly proportional to the quality of the question asked. ☼ Science Quotes 
Ohlbe Hero France, 20090122 10:33 @ Helmut Posting: # 3107 Views: 14,359 

Dear Helmut, » they use some kind of Newspeak. Right, it's not a bug, just a "numeric inaccuracy" Regards Ohlbe 
Ohlbe Hero France, 20090122 22:06 @ Helmut Posting: # 3110 Views: 14,333 

Dear Helmut, My good'ol' long trusted oldfashioned calculator is even worse when using the builtin SD function... OK with 9001, 9002, 9003, but I get an SD of 2 with 90001, 90002, 90003... With longer figures I get either an error code, or a zero. Regards Ohlbe 
Helmut Hero Vienna, Austria, 20090122 23:26 @ Ohlbe Posting: # 3111 Views: 14,332 

Dear Ohlbe, great fun! Like ElMaestro said: “Rubbish in, rubbish out.” In bioanalytics we never see such small CVs, so it doesn’t matter… M$ improves. The calculator of Windows Vista SP1 gives the correct answer even if close to the numeric resolution ( 90000000000000000000000000000001,…) .— All the best, Helmut Schütz The quality of responses received is directly proportional to the quality of the question asked. ☼ Science Quotes 
ElMaestro Hero Denmark, 20090123 12:12 (edited by ElMaestro on 20090123 12:58) @ Helmut Posting: # 3115 Views: 14,298 

Haha, Ohlbe and HS you seem to be having great fun here. Let me add, I know of a famous, fantastic, brilliant and heavenly piece of software for 2,2,2BE calculation in which the programmer circumvented problems of this type by scaling all input data* before the model fitting and ANOVA takes place. There is one potential drawback of doing that, however: In a model fit you need differentials of the dF/dX type, possibly also double differentials. In order to do that the value of the delta (d above) should be chosen VERY wisely. It should in theory be infinitely small, but computers of course cannot do that, so we try e.g. 10e4 or 10e6 or whatever. But if the scaling causes the numbers in the input dataset to be of a magnitude close to the delta then everything could be screwed up. The pros and hardliners use Al Gore Rythms with variable delta's in their matrices and differentials. The programmer behind the software mentioned above didn't do that kind of fancy stuff, mainly because of the log operation. Would still perhaps be interesting to see if a BEscript in R or SAS can be challenged by datasets with very large or very small numbers (perhaps even worse: When the range of values in the input dataset is narrow). Since I don't have SAS I cannot do it all, but I might try a few R challenges one of these days. EM. *: All data divided by the highest data entry in the entire dataset. All values then between 0 and 1. 
Ohlbe Hero France, 20090124 19:44 @ ElMaestro Posting: # 3120 Views: 14,200 

» I know of a famous, fantastic, brilliant and heavenly piece of software for 2,2,2BE calculation... It reminds me of another fantastic, brilliant and heavenly piece of software developed by the same programmer, which worked beautifully on his computer, but bugged on mine with the very same set of data... Conclusion: software or spreadsheet validation is only valid under a specific environment, and needs to be repeated if you change for another OS, or another version of the same OS ! Consequence: if a provider sells you a piece of software as "validated", just forget what he said, unless he also sells you the same computer and environment he used for his validation. Regards Ohlbe — Regards Ohlbe 
martin Senior Austria, 20090121 12:16 @ Helmut Posting: # 3084 Views: 14,495 

dear all ! a note on data exchange formats. in general data are used as they appear on the page but with electronic flies the situation is less obvious. some electronic file format is ambiguous concerning correct interpretation and presentation of the file contents. in MS excel, numbers may rounded internally (for example you see 2.35 but the correct number is 2.354324). additionally, the file content is unclear and may differ from one computer system to other (or between different versions of MS excel). there is also the potential risk of macro viruses with MS excel. for this reasons, file formats that do not suffer from these aliments should be used. I personally prefer to use CSV (ASCII) files which is an open format and you will be able to read this file errorfree in 10 or more years  which may not be the case with the current or older excel (MS proprietary) format. Best regards Martin 
Helmut Hero Vienna, Austria, 20090121 12:31 @ martin Posting: # 3085 Views: 14,448 

Dear Martin! » […] in general data are used as they appear on the page but with electronic flies the situation is less obvious. some electronic file format is ambiguous concerning correct interpretation and presentation of the file contents. in MS excel, numbers may rounded internally (for example you see 2.35 but the correct number is 2.354324). Electronic flies? See one of my lectures (slides 6566) concerning rounding. If results have to be recalculated from printouts, results may differ if the original evaluation was based on full precision data  which may lead to avoidable discussions… » I personally prefer to use CSV (ASCII) files which is an open format […] Wonderful! This is exactly what I want from my clients. Additionally an MD5checksum should be used to maintain data integrity. If datafiles are attached to an email, the checksum should be sent in a separate email. — All the best, Helmut Schütz The quality of responses received is directly proportional to the quality of the question asked. ☼ Science Quotes 
SDavis Regular UK, 20090122 11:56 @ Helmut Posting: # 3108 Views: 14,547 

Hi Helmut, » WinNonlin 5.2.1 » STDEV(90000001;90000002;90000003) = 0 Just saw your post on this  I managed to replicate it in a workbook using a cell formula, however this is not how I'd recommend people perform such data manipulations in WinNonlin. Performing the same calculation using either Descriptive Stats or the summary stats within Table Wizard will give you the correct answer i.e. 1. (as indeed will your worked example of calculating it without using the SD function). I would recommend any WinNonlin users to use the Tools functions I described above since the 'grid' calculations come from, I think FormulaOne, which is a third party tool we use that seems to be susceptible to this error. Incidentally I don't think this should be a problem with Version 6 since it's not using this data grid anymore. Cheers, Simon — Simon Senior Scientific Consultant Pharsight  A Certara™ Company Simon Davis at LinkedIn Forthcoming meetings and training Problems are not stop signs, they are guidelines. Robert H. Schuller 
Helmut Hero Vienna, Austria, 20090121 01:02 @ Ohlbe Posting: # 3079 Views: 14,666 

Dear Ohlbe! » […] using […] R to calculate the mean of 6 values would be like using a sledgehammer to kill a mosquito, and would seriously reduce the number of possible users. Hhm, the only hurdle I see are the sometimes strange import routines. For R novices there's a nice GUI, the R Commander. Import from a textfile, the clipboard, an URL, SPSS, Minitab, Stata, Excel, Access, or dBase is just a few mouseclicks away. You may perform fairly sophisticated statistics right there (definitely far beyond Excel's capabilities already), or simply type at the command line sd(c(90000001, 90000002, 90000003)) That's all  not a sledgehammer to kill a mosquito, but just a tiny pin. See here concerning validation. — All the best, Helmut Schütz The quality of responses received is directly proportional to the quality of the question asked. ☼ Science Quotes 
ElMaestro Hero Denmark, 20090122 19:32 @ Helmut Posting: # 3109 Views: 15,666 

Garbage in, garbage out. It's very very simple. EM. — I could be wrong, but… Best regards, ElMaestro  since June 2017 having an affair with the bootstrap. 
ElMaestro Hero Denmark, 20090123 22:45 @ Helmut Posting: # 3119 Views: 14,233 

Dear Helmut, » That's all  not a sledgehammer to kill a mosquito, but just a tiny pin. Aha... Tomorrow somebody's going to ask how to get and use the R commander without downloading the entire R package. Shall we just say the R commander is a nice tool but the mozzies are still being given a thorough beating in the background by a sledge hammer with bells and whistles when the R commander is running. EM. 
Aceto81 Regular Belgium, 20090126 16:15 @ ElMaestro Posting: # 3122 Views: 14,194 

» Shall we just say the R commander is a nice tool but the mozzies are still being given a thorough beating in the background by a sledge hammer with bells and whistles when the R commander is running. But still I would like to use the sledge hammer when it's free instead of paying a lot of money for the "right" tool. Ace 
d_labes Hero Berlin, Germany, 20090126 16:35 @ Aceto81 Posting: # 3124 Views: 14,176 

Dear Ace » But still I would like to use the sledge hammer when it's free ... Rather dead than slave. (eastfrisian helmet plate, to protect against hostile sledge hammers ) — Regards, Detlew 