On 09/15/2007 02:33 PM, NoOp wrote: > On 09/15/2007 01:37 AM, Robert wrote: >> On Friday 14 September 2007, musomba wrote: >>> Greetings everybody. >>> >>> The spreadsheet is here >>> >>> http://www.ezziarts.com/mullah.xls >>> >>> When opened in excel, the 'balance owed' column data ascends nice and >>> correctly, see screen cap here:- >>> >>> http://www.ezziarts.com/excel.jpg >>> >>> When opened in calc, the 'balance owed' column gets all jumbled up. see >>> screen cap here:- >>> >>> http://www.ezziarts.com/calc.jpg >>> >>> >>> I and every one else at work uses excel, My Boss has begun to use open >>> office on his laptop and wants me to solve this problem. he does not >>> believe its an open office problem. >>> >>> Please help! >> >> When numbers are formatted as text, they cannot be used in calculations or >> formulas. Take a hard look at column J (starting with J49). What format is >> the cell (right click, format cells, look at the number tab)? If you search >> the help file for "formats;changing text/number", you will find instructions >> on how to fix this. The trickiest part is resetting the cell formats to >> non-text. >> >> The fastest way I know of to do this is to select the cells you want to make >> non-text then go Format -> Default Formatting. This will reset the all the >> cell formats, including font, borders, alignment, color, etc. These are easy >> to reset (perhaps it's time to create a cell style) compared to selecting >> each cell individually and changing the format to non-text. >> > > Nope... I have the spreadsheet up on two terminals; 1 is MS Excel, the > other is OOo Calc 2.3. Even when I convert K48, H49, I49, J49 & G49 to > number the cell K49 does not recalculate and show 68 (it still shows 67). > > Even if you enter this in K49 (with all the following formated as numbers): > =(K48+H49+I49+J49) > the result is still 67. > K48=67 > H49=0 > I49=0 > J49=1 > and the result *should* be 68. > > The interesting part is if you reformat cell J49 to numbers, the result > is a "'1" value. If you manually change that to "1" then the formula > works and the result is 68. Note: default formating that cell will also > result in "'1", so the data much be physically re-entered. > > This looks to me like a Calc bug. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ I take that last back... Calc is literally translating the text character "1" and formating it in the cell as "'1" as it should.
Even in Excel if I change the format of J49 to number, it has a popup that warns that the cell is now a literal and provides a nice little feature/dropdown to allow you to convert the value to a number. The Excel spreadsheet needs to be fixed. Now the question would be; why does Excel automatically translate the text character "1" as a number in it's formala? Gary --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
