17 Jul Create new “calculated” columns using Excel
Posted at 14:18h in Tutorials 0 Comments
It is necessary to have a basic knowledge of the use of Excel to be able to create new columns.
Be careful, it is useless and not recommended to transform numerical variables (e. g. age) to create categories (<20; 20-55; >55 for instance) because this leads to a loss of information. Pvalue.io will offer to do so only if it deems it necessary.
Perform a mathematical operation between several columns
- Insert a column (thereafter, we will call it column X)
- Give a title to this column (cell X1)
- Click on the cell X2
- Write the mathematical formula in the formula bar (remembering to precede it with “=”), using the cell references. For instance, if you want X2 to be the sum of V2 and W2, then the formula to be entered is: = V2 + W2
- Expand the result
Recoding a text column
- Insert a column (thereafter, we will call the new column X and the former one Y)
- Give a title to this column (cell X1
- For each value in column Y, assign a more general value in column X: for example, if we have a column named “fetal ultrasound result”, the value “left ventriculomegaly” could be recoded as “heart defect” if there are several possible heart defects).
If the categories are not exclusive, it will be necessary to create one column per category and recode into as many dummy variables (yes/no) as categories. For example: for a fetal ultrasound, there may be several abnormalities of different organs. I may have filled cell Y1 as follows: “cardiac and brain abnormalities.” I will therefore have to create at least 2 columns: a column “cardiac anomaly” and a column “brain anomaly”.