Table of Contents
Frequency Count in Excel
Sort by String Length
An interesting solution by 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.