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 |

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)

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