Format Excel numbers as GB, MB, KB, B

Here is a great tip for formatting numbers in Excel as Gigabytes, Megabytes, Kilobytes, Bytes, etc.
Use the following formula:

=IF((A1>=POWER(2,30)),
     TEXT((A1/POWER(2,30)),"##0.00\G"),
     IF((A1>=POWER(2,20)),
           TEXT((A1/POWER(2,20)),"##0.00\M"),
           IF((A1>=1024),
               TEXT((A1/1024),"##0.00\K"),
               TEXT((A1),"##0.00\B")
               )
         )
    )

The same formula will work for Gigabits, Megabits, etc. assuming you start with bits instead of bytes. If you want to convert from bits to bytes in the process, use this formula:

=IF((A1>=POWER(2,33)),
     TEXT((A1/POWER(2,33)),"##0.00\G"),
     IF((A1>=POWER(2,23)),
           TEXT((A1/POWER(2,23)),"##0.00\M"),
           IF((A1>=8192),
               TEXT((A1/8192),"##0.00\K"),
               TEXT((A1/8),"##0.00\B")
               )
         )
    )

Tags: excel, gigabytes megabytes, gt power, megabytes kilobytes

This entry was posted on February 5, 2007 at February 5, 2007 and is filed under Tips. You can follow any responses to this entry through the RSS 2.0 feed.