Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
fuss:excel [2014/12/19 22:45]
127.0.0.1 external edit
fuss:excel [2017/02/22 18:30] (current)
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:
 +<code excel>
 +=ABS(OFFSET(C2,​ -1, -2, 1, 1)-OFFSET(C2,​ 0, -2, 1, 1))
 +</​code>​
 +
 +in order to avoid negative values.

fuss/excel.txt · Last modified: 2017/02/22 18:30 (external edit)

Access website using Tor Access website using i2p


For the copyright, license, warranty and privacy terms for the usage of this website please see the license, privacy and plagiarism pages.