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.

WEEKNUM() for Google Docs & Spreadsheets

I've been using Google Docs & Spreadsheets for some time now. Mostly the spreadsheets, though. During that time I've made some relative complex spreadsheets, and I'm really pleased with them.
However there still are a few normal spreadsheet commands missing, and yesterday I was in need for exactly one of them: WEEKNUM() (as detailed in ISO 8601:2000)

What I needed was to get the number of the week in a year (1 to 53) for any specific date.
After some time hacking at the keyboard, I got the following formula to work.
The date to check is located in cell C5.

=INT((C5-DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3)+WEEKDAY(DATE(YEAR(C5-WEEKDAY(C5-1)+4),1,3))+5)/7)

The above imitates the WEEKNUM() command, implemented in most spreadsheets, except on one count:

1. It's ONLY ISO 8601:2000, so there's no option to set the type.

Very usefull for me... perhaps usefull for you, too?




Updated on Feb.23, 2012:

Also thanks to Snezy and Anonymous for the two additional attempts at this. I've tested both of the forumlas you posted in the comments, but had some problems making them work. Snezy's seems to not be able to produce one digit week numbers, and Anonymous would display "week" in the output, but based on his input, I did make this formula, which does work both in Google Spreadsheet and Excel, but since it relies on the fairly undocumented TEXT() function, it may not work on all other Spreadsheets:

=LEFT(TEXT(C5; "w d"); 2)

Pretty neat!