# Differences

This shows you the differences between two versions of the page.

 fuss:excel [2014/12/19 22:45]127.0.0.1 external edit fuss:excel [2017/02/22 18:30] (current) Both sides previous revision Previous revision 2014/12/25 20:24 office [Sort by String Length] 2014/12/19 22:45 external edit2014/07/16 18:56 office [Frequency Count in Excel] 2014/01/15 20:02 external edit Next revision Previous revision 2014/12/25 20:24 office [Sort by String Length] 2014/12/19 22:45 external edit2014/07/16 18:56 office [Frequency Count in Excel] 2014/01/15 20:02 external edit Line 1: Line 1: + ====== Frequency Count in Excel ====== + {{fuss_excel_statistical_distributions_using_frequency.gif}} + + ====== Sort by String Length ====== + + An interesting solution by [[http://​simon.schönbeck.dk/​excel-sort-by-length-of-string/​|Simon Schönbeck]] that also works in Google spreadsheet. + + Suppose you have some strings in a sheet with different lengths. All you need to do is to create a second column, select the first cell, insert the function ''​=LEN(A1)''​ and then drag down: + + ^ A          ^ B  ^ + | absdfsdfsd | 10 | + | cdef       | 4  | + | rwerwer ​   | 7  | + | eee        | 3  | + | w          | 1  | + | sdfd       | 4  | + + Now, by selecting both columns, go to ''​Data->​Sort Range''​ and sort by column ''​B''​. Since column ''​B''​ contains numbers representing the length of the strings in column ''​A'',​ column ''​A''​ will be sorted as well by string length. + + ^ A ^ B ^ + |w | 1 | + |eee | 3 | + |cdef | 4 | + |sdfd | 4 | + |rwerwer | 7 | + |absdfsdfsd | 10 | + + ====== Pairwise Difference between Cells ====== + + The pairwise difference between cells in a column can be calculated using the ''​OFFSET''​ excel command: + + ^ Values ^ Pairwise Difference ^ + | 9      |            | + | 5      | ''​=OFFSET(C2,​ -1, -2, 1, 1)-OFFSET(C2,​ 0, -2, 1, 1)''​ | + | 10     ​| ​           | + | 3      |            | + + Applying the formula to all rows (by dragging), will result in the following table: + + ^ Values ^ Pairwise Difference ^ + | 9      |                     | + | 5      | 4                   | + | 10     | -5                  | + | 3      | 7                   | + + You can also use ''​ABS''​ in the offset formula: + + =ABS(OFFSET(C2,​ -1, -2, 1, 1)-OFFSET(C2,​ 0, -2, 1, 1)) + ​ + + in order to avoid negative values.