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.
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.
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")
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.
Wicked helpful! Thanks!
ReplyDeleteThanks mate, this saved me hours of work!
ReplyDeletevery useful, I modified this to make my own version which handles negative numbers, works from e-24 to e+24 and is in excel format - thanks!
ReplyDelete=IF(A3>0,"","-")&ROUND(ABS(A3)/CHOOSE(IF(ABS(A3)>1,ROUNDDOWN(LOG(ABS(A3),10)/3,0),ROUNDUP(LOG(ABS(A3),10)/3,0))+9,10^-24,10^-21,10^-18,10^-15,10^-12,10^-9,10^-6,10^-3,1,10^3,10^6,10^9,10^12,10^15,10^18,10^21,10^24),2)&CHOOSE(IF(ABS(A3)>1,ROUNDDOWN(LOG(ABS(A3),10)/3,0),ROUNDUP(LOG(ABS(A3),10)/3,0))+9,"y","z","a","f","p","n","u","m","","K","M","G","T","P","E","Z","Y")
Any idea how to reverse this? I have a set of number that I need to add that are already in the target format. As an example 5.24m 2.1g etc
ReplyDeleteTo reverse the SI numbers you can use this formula:
ReplyDelete=if(isnumber(value(mid(C4,len(C4)-1,1))),
value(LEFT(C4,len(C4)-1)),
value(left(C4,len(C4)-2))*choose(find(mid(C4,len(C4)-1,1),"pnumkMGT"),10^-12,10^-9,10^-6,10^-3,10^3,10^6,10^9,10^12)
)
Place SI value at C4.
Quick insight:
Line one determines if the second-last digit is a number or a char.
Line two is used if the second last digit is a number. This covers the case where you have a single digit postfix, such as "m".
Line three is used if the second last digit is a number. It simply looks up the last digit, and then multiply the with the equivalent factor.
I've tested with the following values:
987pm
987nm
987um
987mm
987m
987km
987Mm
987Gm
987Tm
Have not stress tested it, but hope it will cover your needs.