Converting Column Values - KB, MB, GB, TB

[sfscottca]

I would like my column to format automatically for me. I found this on the web, but excel doesn't recognize it as a valid formula. Please help me identify what's wrong with it...Thanks!

=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.

 

[shg]

Try replacing the semicolons with commas.

 

[shg]

Or you could use instead,

=ROUND(A2/1024^INT(LOG(A2,1024)), 1) & INDEX({"","ki","Mi","Gi","Ti","Pi"}, LOG(A2,1024)+1) & "B"

 

[sfscottca]

You're awesome! I replaced semicolons with commas and it worked. Your formula is even shorter and works perfect. THANKS!

 

[shg]

Yer welcome, good luck.