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:

Input Value
1u 1.00E-06
10u 10.00E-06
100u 100.00E-06
1m 1.00E-03
10m 10.00E-03
100m 100.00E-03
1 1.00E+00
10 10.00E+00
100 100.00E+00
1k 1.00E+03
10k 10.00E+03
100k 100.00E+03
1M 1.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.

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.