Tag: spreadsheet

  • Using SI prefixes/multipliers in spreadsheets

    Note: I’ve lightly tested this with Microsoft Excel (Windows 10), Excel Online, Google Sheets and LibreOffice Calc. It seems to work. Like all spreadsheet data conversions, please verify before trusting your PhD thesis tables to it …

    Asked on the GTALUG mailing list the other week:

    Does anybody know how to display and work with SI numbers like 10k or 20M or 40G within LibreOffice?

    I came up with the following formula, in this example for data in cell D3:
    =IF(LEN(T(D3))=0, D3, CONVERT(VALUE(LEFT(D3, LEN(D3) - 1)),
        RIGHT(D3, 1) & "m", "m"))

    which results in:

    InputValue
    1u1.00E-06
    10u10.00E-06
    100u100.00E-06
    1m1.00E-03
    10m10.00E-03
    100m100.00E-03
    11.00E+00
    1010.00E+00
    100100.00E+00
    1k1.00E+03
    10k10.00E+03
    100k100.00E+03
    1M1.00E+06

    The right column is displayed in LibreOffice Calc’s newly(ish)-supported engineering notation.

    This function works through creative (mis-)use of the CONVERT() function:

    1. if the argument is a numeric value, pass it through;
    2. if the argument is a string, return CONVERT(«numeric part», "«prefix»m", "m"). This is lightly misusing overloading the unit conversion function by going via metres, but it saves having a lookup table.

    This function doesn’t work with IEC 60027-2 binary prefixes, but they’re silly and I wouldn’t be caught dead using ’em.

  • LibreOffice brings the old

    Twenty-two years ago, I wrote a thesis. It wasn’t a very good thesis, but it did what it needed to do. For years, its model files have been unreadable, because the spreadsheets were written in a ~1992 version of Microsoft Works. These are old files:

    1993-04-21 03:19 newmodel.wks
    1993-04-21 03:19 newmodel.wk1
    1993-04-08 06:29 pr_fa.wks

    Quite recently, LibreOffice realized that there are old files out there that (unlike my thesis models) could still be useful. As they have no commercial requirement to only support the latest and greatest, LibreOffice added the ability to read these ancient works. So my old stuff lives again:

    ms works files from 1993

    I found a screen dump that I used back in ’93 to illustrate the layout. The display was colour, but here it is brought back to life with a little bit of antialiasing:

    '93 print stylee

    LibreOffice can also read old AppleWorks files. Although Works 6 still runs on Catherine‘s Mac, it looks a bit … dated:

    appleworks

    Thanks, LibreOffice! It’s sometimes easy to forget (like right after updated to Ubuntu 15.04, which decided that BlueTooth support was kinda optional unless you jumped through hoops …) that people do write software just to be more useful.

  • nerdy spreadsheet tick/cross formatting

    Screenshot from 2014-11-22 08:13:57

    The magic custom format string for this is:

    [Red][=0]✗;[Black][<>0]

    Works with LibreOffice and Excel on every platform I’ve tried.

  • excel: alpha code to numbers

    I use an annoying program that labels its output A..Z, AA..AZ, BA … rather than numerically from 1. This is annoying, as a spreadsheet won’t sort it correctly (it does A, AA, AB …). The following code will convert this code to the right numbers, assuming your alpha code is in cell B3:

    =IF(LEN(B3)=1,CODE(B3)-64,26*(CODE(B3)-64)+CODE(RIGHT(B3))-64)

    This will only work for codes of two characters or less, and is case sensitive.