Excel Analysis

This wiki is designed to help anyone perform statistical analyses on their data using Excel.

Transpose

Transpose -when copying and pasting, can switch the orientation such that columns become rows/rows become columns

{{#ev:youtube|IRqp09ypYtI|500|Here is a description of the video}}

Filter

Filter e.g., have a bunch of 0s or only want values of 1, can also select unique records only

{{#ev:youtube|8_wBiLnQWu4|500|Here is a description of the video}}

Keyboard Shortcuts

keyboard shortcuts 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

Pivot Table -design-> subtotals off, grand totals off -can easily switch ordering -the following shows how to use a pivot table on attentional blink data to get average accuracy for T2 by the factors a) distractor type and b) lag, and only when T1 has a correct response:

{{#ev:youtube|K5EnWu4AZ8U|500|Here is a description of the video}}

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

-use 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?)

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))

Other Notes:

-copying or duplicating a sheet won't copy over new chart references, the charts will still reference old positions -do not use the default error bars option that excel provides, use custom error bars and point to your own calculations