On Mon, 2007-10-01 at 14:58 +0100, Brian Barker wrote: > At 14:18 01/10/2007 +0200, Lars Forseth wrote: > >I have this problem: I get text files from various programs which I > >want to use in various statistics programs; like WinBASP, PAST or R. > >Sometimes these files contain blank fields; ie when I import them in > >calc there are blank cells, (nodata). Some of the statistics > >software does not like this; and I would like to change the blank > >cells to 0. I would like to use calc for this because it is also > >nice to be able to use these files in a spreadsheet. > > > >Does anyone have a solution to suggest? I have googled the problem, > >but got no obvious solution. Sine the data are in several columns > >some sort of use of matrix and IF and ISBLANK functions is probably > >necessary, but I am stuck. I do not program in Basic, but can handle > >simple tasks in Python > > o Enter zero into a spare cell somewhere. > o Copy that cell to the clipboard. > o Now select all your original data and use Edit | Paste Special... > (or right-click | Paste Special... or just Ctrl+Shift+V) to paste > this zero over it. In the Paste Special dialogue, select Add under > Operations; also remove the tick from "Paste all" if necessary, and > then make sure that Formats is not ticked but Numbers is. This has > the effect of adding zero to all your values, but will have the > incidental effect of changing any blank cell to an explicit zero. > > If all your values are numbers or text, you don't have to worry about > Formats not being ticked, in fact: this is necessary only to avoid > corrupting any date values there may be. > > I trust this helps. > > Brian Barker
I use OOo Calc all the time to convert .xls files to .csv files for input to R. Blank cells are AUTOMATICALLY assumed to be "NA's" when the .csv file is input to R. If your blank cells really represent "no data" or "not available" (i.e., missing data), then you DON'T want to change them to zero!!!! Changing missing data to zero will definitely screw up the computations. (A missing value is unknown and you can't assume it's zero or any other particular value. How missing values are handled will depend on the statistical methodology.) Rick B. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
