 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2005Non-numeric Data
Hi All, I'm trying to run descriptive statistics on data that has been copied and pasted from a .csv file but it says some of the data is non numeric. i've used the view cell contents download, i've tried trim and clean, i've tried multiplying everything by 1, all without success and i'm going mad here. Does anyone have any ideas? Thanks in anticipation, Angela You could enter the formula =VALUE(A1) in a spare column, Format the cell to Number with appropriate decimal places, and copy the formula down. Do this for the other columns of data. Fix the values of the formulae by highlighting them all and using <Copy>, Edit | Paste Special | Values | OK and <enter> Then you can delete all the original columns. Pete Thank you- this works for some cells but not all. It works for cells that are truly empty but some seem to have something in them even after i try to remove all contents. does that make sense? > You could enter the formula =VALUE(A1) in a spare column, Format the > cell to Number with appropriate decimal places, and copy the formula [quoted text clipped - 4 lines] > > Pete What do you get from =CODE(cellAddress) Jerry > Thank you- this works for some cells but not all. It works for cells that are > truly empty but some seem to have something in them even after i try to [quoted text clipped - 8 lines] >> >>Pete mostly 2-digit integers: 48,32,50,51,45 but some return #VALUE! > What do you get from =CODE(cellAddress) > [quoted text clipped - 12 lines] > >> > >>Pete 32 is a space character 48-57 are digits, but those cells should not look empty. #VALUE! is a truly empty cell None of these should be problematic for the functions (AVERAGE, STDEV, etc) that calculate descriptive statistics. However, the spaces would be problematic for the ATP Descriptive Statistics tool. Elsewhere in the thread, you indicate that you tried to remove spaces with the TRIM() function. That would produce a zero length string, not an empty cell. ATP will not tolerate strings, even those of zero length. You will have to actually remove the cell contents. Jerry > mostly 2-digit integers: 48,32,50,51,45 but some return #VALUE! > [quoted text clipped - 14 lines] >>>> >>>>Pete > Hi All, > I'm trying to run descriptive statistics on data that has been copied and > pasted from a .csv file but it says some of the data is non numeric. > i've used the view cell contents download, i've tried trim and clean, i've > tried multiplying everything by 1, all without success and i'm going mad > here. Try opening the CSV file directly in a new spreadsheet. There should be an option to choose the format of the columns. Alternatively select the imported data then choose Edit -> Goto -> Special then select Constants and uncheck all the options other than "Numbers". That should select only the numerical values. While they are selected choose a different colour for the text and background so any non-numerical cells will stand out. Are you using the correct range to work out the statistics? the 'go to/special' tip is good but what do i do with the non-numerical data when i highlight it? These are spreadsheets with 65000 rows of data by 20 columns so manually deleting cells is not an option. > > Hi All, > > I'm trying to run descriptive statistics on data that has been copied and [quoted text clipped - 13 lines] > > Are you using the correct range to work out the statistics? > the 'go to/special' tip is good but what do i do with the non-numerical > data > when i highlight it? These are spreadsheets with 65000 rows of data by 20 > columns so manually deleting cells is not an option. Hmmm. Can you find just a sample of the invalid data and are you able to see why it's invalid? The problem seems to be that I have cells which look empty but some of them contain spaces. When I use trim to clear out the spaces, it still doesn't solve the problem. I don't know if it is because not all the blank cells are blank in the same sense > > the 'go to/special' tip is good but what do i do with the non-numerical > > data [quoted text clipped - 3 lines] > Hmmm. Can you find just a sample of the invalid data and are you able to see > why it's invalid? > The problem seems to be that I have cells which look empty but some of > them > contain spaces. When I use trim to clear out the spaces, it still doesn't > solve the problem. I don't know if it is because not all the blank cells > are > blank in the same sense Use Find & Replace to erase spaces: In the "Find" box type a space, in the "Replace" box just delete whatever is in there to leave it empty. That should scrub all spaces in your numerical data, including cells with one space in them. Make sure "find entire cells only" is unchecked. If that doesn't work then it shouldn't be too hard to write a macro to scan through the worksheet looking for non-numerical data in the selected cells. Thanks Danny- that sounds just what I'm looking for. Angela > > The problem seems to be that I have cells which look empty but some of > > them [quoted text clipped - 11 lines] > If that doesn't work then it shouldn't be too hard to write a macro to scan > through the worksheet looking for non-numerical data in the selected cells. Angela Could be you have the HTML non-breaking space Character 160 See David McRitchie's TrimAll code. http://www.mvps.org/dmcritchie/excel/join.htm#trimall Gord Dibben Excel MVP >The problem seems to be that I have cells which look empty but some of them >contain spaces. When I use trim to clear out the spaces, it still doesn't [quoted text clipped - 8 lines] >> Hmmm. Can you find just a sample of the invalid data and are you able to see >> why it's invalid? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.