Use SI number notation in spreadsheets

I've long been annoyed that spreadsheets (Excel and Google Docs) lack the option to format a number using SI number notation.

To get around this limitation, I've made the following three formulas.

SI-Notation numbers:

To write SI notation numbers in the range from 1E14 (T) to 1E-10 (p), the following formula will both display the rounded number and the SI-notation, as well as adding the unit as well.

Input number is in cell A3.

=ROUND(A3/CHOOSE(IF(A3>1;ROUNDDOWN(LOG(A3;10)/3;0);ROUNDUP(LOG(A3;10)/3;0))+5;10^-12;10^-9;10^-6;10^-3;1;10^3;10^6;10^9;10^12);0)&
CHOOSE(IF(A3>1;ROUNDDOWN(LOG(A3;10)/3;0);ROUNDUP(LOG(A3;10)/3;0))+5;"p";"n";"u";"m";"";"k";"M";"G";"T")&"m" 


 The rounded number 
 Number of decimals 
 The SI-notation 
 Unit 

Negative numbers will result in #NUM.
Numbers outside of the range will result in #VALUE.
If A3 contains 1568, the above formula will result in the value 2km.


For computer-memory and storage, two other formulas are missing, namely conversion from bytes to kilobytes, megabytes ect. for both base-2 and base-10.


Base-10 display uses kB, MB, TB etc. as post-fix:

This notation is primarily used by storage manufactures (although the letters used to be used for base-2 values).


Input number is in cell A4.


=ROUND(A4/CHOOSE(ROUNDDOWN(LOG(A4;10)/3;0)+1;1;10^3;10^6;10^9;10^12;10^15);1)&
CHOOSE(ROUNDDOWN(LOG(A4;10)/3;0)+1;"B";"kB";"MB";"GB";"TB";"PB") 


 The rounded number 
 Number of decimals 
 The SI-notation 



Negative numbers will result in #NUM.
Numbers outside of the range will result in #VALUE.
If A4 contains 140000, the above formula will result in the value 140.0kB.

Base-2 display uses kiB, MiB, TiB etc. as postfix:


This notation is the one primarily used by any computer system.



Input number is in cell A5.

=ROUND(A5/CHOOSE(ROUNDDOWN(LOG(A5;2)/10;0)+1;1;2^10;2^20;2^30;2^40;2^50);1)&
CHOOSE(ROUNDDOWN(LOG(A5;2)/10;0)+1;"B";"kiB";"MiB";"GiB";"TiB";"PiB")

 The rounded number 
 Number of decimals 
 The SI-notation 



Negative numbers will result in #NUM.
Numbers outside of the range will result in #VALUE.
If A5 contains 140000, the above formula will result in the value 136.7kiB.

With these formulas you should be pretty safe for any value you need represented. If not the structure of the formulas should easily allow you to modify them for your own usage.