From the school of There’s Probably a Better Way, But…
I was taking the output of SysInternals’ du.exe utility and wanted to email it to a colleague, but the folder sizes were in KB. Since most of them were many Gigabytes in size, I wanted a quick way to convert them to the appropriate size in GB, MB, or KB.
I copied the du.exe size column and pasted it into Excel. Then I created the following formula:
=IF(E6>(2^20),TEXT(E6/(2^20),"0.0") & " GB","smaller")
…where E6 is the cell reference. Also note that du.exe’s output was in KB, so my test is one order of magnitude smaller than it would need to be if I was formatting a size in bytes. (i.e., 2^20 is 1 MB).
I then nested another similar if() function with a final formatting option to get my final formula:
=IF(E6>(2^20),TEXT(E6/(2^20),"0.0") & " GB",IF(E6>(2^10),TEXT(E6/(2^10),"0.0") & " MB",TEXT(E6,"0.0") & " KB"))
