Difference between revisions of "Excel Analysis"
(22 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
This wiki | This wiki tips and tricks from the Bar Lab about how to work with data using Excel. Click on links to get more detailed info on a topic. Note on videos: set the quality to 720p HD. | ||
===Keyboard Shortcuts=== | |||
[http://office.microsoft.com/en-001/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx 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 | |||
====Transpose | ===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 | |||
===Transpose=== | |||
[http://office.microsoft.com/en-001/excel-help/switch-transpose-columns-and-rows-HP010224502.aspx Transpose] | [http://office.microsoft.com/en-001/excel-help/switch-transpose-columns-and-rows-HP010224502.aspx Transpose] | ||
-when copying and pasting, can switch the orientation such that columns become rows/rows become columns | -when copying and pasting, can switch the orientation such that columns become rows/rows become columns | ||
Line 8: | Line 19: | ||
<Center>{{#ev:youtube|IRqp09ypYtI|500|Here is a description of the video}}</Center> | <Center>{{#ev:youtube|IRqp09ypYtI|500|Here is a description of the video}}</Center> | ||
===Filter=== | |||
[http://office.microsoft.com/en-001/excel-help/filter-data-in-a-range-or-table-HP010073941.aspx Filter] | [http://office.microsoft.com/en-001/excel-help/filter-data-in-a-range-or-table-HP010073941.aspx Filter] | ||
e.g., have a bunch of 0s or only want values of 1, can also select unique records only | e.g., have a bunch of 0s or only want values of 1, can also select unique records only | ||
<Center>{{#ev:youtube|8_wBiLnQWu4|500|Here is a description of the video}}</Center> | |||
===Pivot tables=== | |||
[http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx Pivot Table] | |||
-design-> subtotals off, grand totals off | -design-> subtotals off, grand totals off | ||
-can easily switch ordering | -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: | |||
<Center>{{#ev:youtube|K5EnWu4AZ8U|500|Here is a description of the video}}</Center> | |||
===Formulas=== | |||
====If statements==== | ====If statements==== | ||
-can | [http://office.microsoft.com/en-001/excel-help/if-function-HP010069829.aspx If function] | ||
-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 | -if statements are structured as follows: =if([condition], [value if true], [value if false]) | ||
-the values can consist if statements, and conditions can have multiple ANDs and ORs, so if statements can get quite elaborate | |||
-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 | -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 | There's also countif and averageif commands, etc. useful to only count all the 4s or to average only certain values | ||
====Freeze panes | ====Concatenate==== | ||
-link together two values | |||
====Static referencing in formulas==== | |||
-use the $ sign before row and column reference to keep it static, useful for computing z-scores, or to compute error bars (see [[#graphs with error bars|graphs with error bars]]) | |||
===Freeze panes=== | |||
-freeze headers in same location so you can see them even when scrolling down | -freeze headers in same location so you can see them even when scrolling down | ||
<Center>{{#ev:youtube|dYW5Lnl8DLA|500|Here is a description of the video}}</Center> | |||
===Quick binning of continuous data (e.g., RTs) for histograms=== | |||
-use the ceiling command | -use the ceiling command | ||
-e.g., =ceiling([cell]/20,1)*20 to place the value into one of twenty bins (?) | -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 (?) | alternatively, =ceiling([cell],6) to place the value into one of 6 bins (?) | ||
===vlookup=== | |||
-useful for quickly looking up and pasting corresponding values from a table | -useful for quickly looking up and pasting corresponding values from a table | ||
=== | ===graphs with error bars=== | ||
-format data series -> add secondary axis | -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 | -layout -> error bars -> more error bar options (don't use "error bar", it won't give meaningful error bars | ||
-to compute your own standard error bars, use the formula: (score-mean)/sqrt(count) | |||
-confidence intervals can be achived the same way by selecting confidence intervals you've selected | -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 | -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 | -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)) | -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 | -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 | -do not use the default error bars option that excel provides, use custom error bars and point to your own calculations |
Latest revision as of 19:01, 21 January 2014
This wiki tips and tricks from the Bar Lab about how to work with data using Excel. Click on links to get more detailed info on a topic. Note on videos: set the quality to 720p HD.
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
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
Transpose
Transpose -when copying and pasting, can switch the orientation such that columns become rows/rows become columns
Filter
Filter e.g., have a bunch of 0s or only want values of 1, can also select unique records only
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:
Formulas
If statements
If function -if statements are structured as follows: =if([condition], [value if true], [value if false]) -the values can consist if statements, and conditions can have multiple ANDs and ORs, so if statements can get quite elaborate -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
Concatenate
-link together two values
Static referencing in formulas
-use the $ sign before row and column reference to keep it static, useful for computing z-scores, or to compute error bars (see graphs with error bars)
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 (?)
vlookup
-useful for quickly looking up and pasting corresponding values from a table
graphs with error bars
-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 -to compute your own standard error bars, use the formula: (score-mean)/sqrt(count) -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