Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
fuss:excel [2014/12/25 20:24] – [Sort by String Length] officefuss:excel [2022/04/19 08:28] (current) – external edit 127.0.0.1
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.1419539044.txt.bz2 · Last modified: 2014/12/25 20:24 by office

Access website using Tor Access website using i2p Wizardry and Steamworks PGP Key


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