Difference between revisions of "Excel Analysis"
(Created page with "This wiki is designed to help anyone perform statistical analyses on their data using Excel.") |
|||
Line 1: | Line 1: | ||
This wiki is designed to help anyone perform statistical analyses on their data using Excel. | This wiki is designed to help anyone perform statistical analyses on their data using Excel. | ||
Transpose | |||
-when copying and pasting, can switch the orientation such that columns become rows/rows become columns | |||
Filter | |||
e.g., have a bunch of 0s or only want values of 1, can also select unique records only | |||
moving around quickly with keyboard shortcuts: | |||
-ctrl+A within section of data, selects all the data only (not the whole worksheet) | |||
-ctrl+arrow keys, moves to beginning/end of row/column | |||
-ctrl+shift+arrow keys moves and selects to beginning/end of row/column | |||
-ctrl+shift+{plus sign} adds row | |||
-ctrl+space adds column | |||
Pivot tables | |||
-design-> subtotals off, grand totals off | |||
-can easily switch ordering | |||
If statements | |||
-can contain if statements as well as multiple ANDs and ORs | |||
-you can use the not empty logic to preserve empty spaces with your formulas, e.g., =if([datacell] <> "", formula to be applied, "") - this means if the cell is not blank, apply the formula, otherwise leave it blank | |||
-isnumber is alternative to the above, as well as istext; other iswhatevers as well | |||
There's also countif and averageif commands, etc. useful to only count all the 4s or to average only certain values | |||
Freeze panes | |||
-freeze headers in same location so you can see them even when scrolling down | |||
Quick binning of continuous data (e.g., RTs) for histograms using the ceiling command | |||
-e.g., =ceiling([cell]/20,1)*20 to place the value into one of twenty bins (?) | |||
alternatively, =ceiling([cell],6) to place the value into one of 6 bins (?) | |||
Static referencing in formulas | |||
-use the $ sign before row and column reference to keep it static, useful for computing z-scores (better way to quickly do z-scores?) | |||
Note: | |||
-copying or duplicating a sheet won't copy over new chart references, the charts will still reference old positions | |||
vlookup | |||
-useful for quickly looking up and pasting corresponding values from a table | |||
concatenate | |||
-link together two values | |||
autofill a pattern, e.g., given 1 2 3, you can copy and paste 1 2 3 4 5 6 etc. using the little square | |||
graphing | |||
-format data series -> add secondary axis | |||
-layout -> error bars -> more error bar options (don't use "error bar", it won't give meaningful error bars | |||
-confidence intervals can be achived the same way by selecting confidence intervals you've selected | |||
cse | |||
-command shift enter, allows formulas to behave like arrays in matlab | |||
-sum all values when column n=1 and column i=1, =sum(bla bla) then hit control+shift+enter | |||
-average(indirect(aw1&av2):indirect("ak"&aw2)) |
Revision as of 01:19, 22 November 2013
This wiki is designed to help anyone perform statistical analyses on their data using Excel.
Transpose
-when copying and pasting, can switch the orientation such that columns become rows/rows become columns
Filter
e.g., have a bunch of 0s or only want values of 1, can also select unique records only
moving around quickly with keyboard shortcuts: -ctrl+A within section of data, selects all the data only (not the whole worksheet) -ctrl+arrow keys, moves to beginning/end of row/column -ctrl+shift+arrow keys moves and selects to beginning/end of row/column -ctrl+shift+{plus sign} adds row -ctrl+space adds column
Pivot tables -design-> subtotals off, grand totals off -can easily switch ordering
If statements -can contain if statements as well as multiple ANDs and ORs -you can use the not empty logic to preserve empty spaces with your formulas, e.g., =if([datacell] <> "", formula to be applied, "") - this means if the cell is not blank, apply the formula, otherwise leave it blank -isnumber is alternative to the above, as well as istext; other iswhatevers as well There's also countif and averageif commands, etc. useful to only count all the 4s or to average only certain values
Freeze panes
-freeze headers in same location so you can see them even when scrolling down
Quick binning of continuous data (e.g., RTs) for histograms using the ceiling command
-e.g., =ceiling([cell]/20,1)*20 to place the value into one of twenty bins (?)
alternatively, =ceiling([cell],6) to place the value into one of 6 bins (?)
Static referencing in formulas -use the $ sign before row and column reference to keep it static, useful for computing z-scores (better way to quickly do z-scores?)
Note: -copying or duplicating a sheet won't copy over new chart references, the charts will still reference old positions
vlookup -useful for quickly looking up and pasting corresponding values from a table
concatenate -link together two values
autofill a pattern, e.g., given 1 2 3, you can copy and paste 1 2 3 4 5 6 etc. using the little square
graphing -format data series -> add secondary axis -layout -> error bars -> more error bar options (don't use "error bar", it won't give meaningful error bars -confidence intervals can be achived the same way by selecting confidence intervals you've selected
cse -command shift enter, allows formulas to behave like arrays in matlab -sum all values when column n=1 and column i=1, =sum(bla bla) then hit control+shift+enter -average(indirect(aw1&av2):indirect("ak"&aw2))