little issue with Excel sorting

I was going nuts trying to sort a little table in Excel which was created using some formulas... Excel was just sorting based on the formula in the cell rather than the cell value.... after googling a lot, I found out that in Excel 2007 the "Calculation" default option is set to "Automatic".... To avoid my problem, I just needed to turn if OFF and switch it to Manual. The option is in "Formulas" tab in Excel 2007, the right most choice "Calculation Options."

Found the answer on a Excel discussion board.

Remove ALL spaces from cells in MS Excel

This blog pertains to data cleaning after you download data from surveyshare.com.

One more thing you MAY have to do when you download your data from surveyshare.com is to remove all unnecessary spaces from certain fields, especially if they happen to be your index variable. I my case I had to do it from the email field  which was used to match the responses to other surveys!!! Here is the macro

 I have found:

Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Selection.Cells
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", " ")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
Next
On Error GoTo 0
End Sub


Really grateful to the author of the macro!!

Excel Macro to convert the CASE of a range of TEXT

Really useful macro. I used it to clean up surveyshare data file before bringing it to SPSS.

Before using:
"Uncomment" (remove the apostrophe from) the line of code that changes the text to the case you want. For example I needed everything to converted to lower case and hence I removed the apostrophe from "' Rng.Value = StrConv(Rng.Text, vbLowerCase)"


Sub ChangeCase()
Dim Rng As Range
On Error Resume Next
Err.Clear
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
' Rng.Value = StrConv(Rng.Text, vbUpperCase)
' Rng.Value = StrConv(Rng.Text, vbLowerCase)
' Rng.Value = StrConv(Rng.Text, vbProperCase)
End If
Next Rng
Application.EnableEvents = True
End Sub

Source: http://www.cpearson.com/excel/ChangingCase.aspx

What is the difference between causation and correlation?

What is the difference between causation and correlation?

One of the most common errors we find in the press is the confusion between correlation and causation in scientific and health-related studies. In theory, these are easy to distinguish — an action or occurrence can cause another (such as smoking causes lung cancer), or it can correlate with another (such as smoking is correlated with alcoholism). If one action causes another, then they are most certainly correlated. But just because two things occur together does not mean that one caused the other, even if it seems to make sense.

World's baby No. 7 billion could be born in UP - Times Of India

World's baby No. 7 billion could be born in UP - Times Of India

dos commands site

http://labnol.blogspot.com/2006/04/useful-windows-xp-dos-commands-tricks.html

Truncating a string variable & other things

This text has been copied from UCLA website!!!

Create a String Variable up that will be the name converted into upper case, lo that will be the name converted to lower case, and sub that will be the third through eighth character in the persons name. Note that we first had to use the string command to tell SPSS that up lo and sub are string variables that will have a length of up to 14 characters. Had we omitted the string command, these would have been treated as numeric variables, and when SPSS tried to assign a character value to the numeric variables, it would have generated an error. We also create len that is the length of the name variable, and len2

that is the length of the persons name.

STRING up lo (A14)
/sub (A6).

COMPUTE up  = UPCASE(name).
COMPUTE lo  = LOWER(name).
COMPUTE sub  = SUBSTR(name,3,8).
COMPUTE len = LENGTH(name).

COMPUTE len2 = LENGTH(RTRIM(name)).

For more info visit: http://www.ats.ucla.edu/stat/spss/modules/functions.htm