Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formatting Cells/Data
Hi, I hope you are doing well.
I have a file I downloaded from our AS/400 (OS400 operating system). I open the file as a text file and go thru the wizard for converting the file to excel format. One of the columns/fields is numeric with some neg and pos numbers and some blank cell contents. During the wizard conversion the field is categorized as DOUBLE with no option to change the field. When I finish the wizard I save the file as an Excel workbook. Then I use the autosum option to calculate the total of the field/column; however, it only picks up on the positive #'s. Hence the total is off by the sum of the negative #'s. I've used the FORMAT cells option to format the column/cells from top to bottom as number but the cells with the negative #'s remain left justified and still are not calculated in the total. Does anyone know what I can do to convert these cells to a number I can use for calculating the total? Thanks in advance. Ken |
#2
|
|||
|
|||
Hi Ken-
I would think the first step is to more closely examine one of the cells containing what should be a negative and determine what is causing the data to be interpreted as text - that's what is preventing the numbers from being subtracted in your total. Once you isolate that it may be a simple matter of find & replace or it may be more involved. HTH |:) "Ken" wrote: Hi, I hope you are doing well. I have a file I downloaded from our AS/400 (OS400 operating system). I open the file as a text file and go thru the wizard for converting the file to excel format. One of the columns/fields is numeric with some neg and pos numbers and some blank cell contents. During the wizard conversion the field is categorized as DOUBLE with no option to change the field. When I finish the wizard I save the file as an Excel workbook. Then I use the autosum option to calculate the total of the field/column; however, it only picks up on the positive #'s. Hence the total is off by the sum of the negative #'s. I've used the FORMAT cells option to format the column/cells from top to bottom as number but the cells with the negative #'s remain left justified and still are not calculated in the total. Does anyone know what I can do to convert these cells to a number I can use for calculating the total? Thanks in advance. Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell formatting behaviour question | Excel Discussion (Misc queries) | |||
Conditional Formatting in Excel Help Please..... | Excel Discussion (Misc queries) | |||
Formatting dates in the future | Excel Worksheet Functions | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |