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.


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.