{"id":15086,"date":"2018-08-07T22:31:29","date_gmt":"2018-08-08T02:31:29","guid":{"rendered":"http:\/\/scruss.com\/blog\/?p=15086"},"modified":"2024-10-25T10:16:41","modified_gmt":"2024-10-25T14:16:41","slug":"using-si-prefixes-multipliers-in-spreadsheets","status":"publish","type":"post","link":"https:\/\/scruss.com\/blog\/2018\/08\/07\/using-si-prefixes-multipliers-in-spreadsheets\/","title":{"rendered":"Using SI prefixes\/multipliers in spreadsheets"},"content":{"rendered":"\n

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

Asked on the GTALUG mailing list the other week:<\/p>\n\n\n\n

\n

Does anybody know how to display and work with SI numbers like 10k or 20M or 40G within LibreOffice?<\/p>\n<\/blockquote>\n\n\n\n

I came up with the following formula, in this example for data in cell D3:<\/pre>\n\n\n\n
=IF(LEN(T(D3))=0, D3, CONVERT(VALUE(LEFT(D3, LEN(D3) - 1)),\n    RIGHT(D3, 1) & \"m\", \"m\"))<\/pre>\n\n\n\n
which results in:<\/p>\n\n\n\n
Input<\/span><\/b><\/td>Value<\/span><\/b><\/td><\/tr>
1u<\/span><\/td>1.00E-06<\/span><\/td><\/tr>
10u<\/span><\/td>10.00E-06<\/span><\/td><\/tr>
100u<\/span><\/td>100.00E-06<\/span><\/td><\/tr>
1m<\/span><\/td>1.00E-03<\/span><\/td><\/tr>
10m<\/span><\/td>10.00E-03<\/span><\/td><\/tr>
100m<\/span><\/td>100.00E-03<\/span><\/td><\/tr>
1<\/span><\/td>1.00E+00<\/span><\/td><\/tr>
10<\/span><\/td>10.00E+00<\/span><\/td><\/tr>
100<\/span><\/td>100.00E+00<\/span><\/td><\/tr>
1k<\/span><\/td>1.00E+03<\/span><\/td><\/tr>
10k<\/span><\/td>10.00E+03<\/span><\/td><\/tr>
100k<\/span><\/td>100.00E+03<\/span><\/td><\/tr>
1M<\/span><\/td>1.00E+06<\/span><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The right column is displayed in LibreOffice Calc’s newly(ish)-supported engineering notation.<\/p>\n\n\n\n
This function works through creative (mis-)use of the CONVERT()<\/code> function:<\/p>\n\n\n\n
    \n
  1. if the argument is a numeric value, pass it through;<\/li>\n\n\n\n
  2. if the argument is a string, return CONVERT(\u00abnumeric part\u00bb, \"\u00abprefix\u00bbm\", \"m\")<\/code>. This is lightly misusing<\/del> overloading the unit conversion function by going via metres, but it saves having a lookup table.<\/li>\n<\/ol>\n\n\n\n
    This function doesn’t work with IEC 60027-2 binary prefixes, but they’re silly and I wouldn’t be caught dead using ’em.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 \u2026 Asked on the GTALUG mailing list the other week: Does anybody know how to display and work with […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[7],"tags":[1739,3144,726],"class_list":["post-15086","post","type-post","status-publish","format-standard","hentry","category-computers-suck","tag-data","tag-si","tag-spreadsheet"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pQNZZ-3Vk","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts\/15086","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/comments?post=15086"}],"version-history":[{"count":4,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts\/15086\/revisions"}],"predecessor-version":[{"id":17663,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts\/15086\/revisions\/17663"}],"wp:attachment":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/media?parent=15086"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/categories?post=15086"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/tags?post=15086"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}