Formatting bytes in a spreadsheet cell

How to format a spreasheet cell to represent byte multiples

It appears that you are using AdBlocking software. The cost of running this website is covered by advertisements. If you like it please feel free to a small amount of money to secure the future of this website.

The following is a cell function that can be used with a spreadsheet application (e.g. MS Excel or LibreOffice Calc) to format a cell number using byte multiples.

The example below uses the content of the cell A1 as input.

For an explanation of IEC Prefixes for binary multiples please consult the guide: Multiples and Submultiples Prefixes Tables.

=IF((A1>=POWER(2,80)),
 CONCATENATE(TEXT((A1/POWER(2,80)),“##0.00”), “ YiB”),
 IF((A1>=POWER(2,70)),
     CONCATENATE(TEXT((A1/POWER(2,70)),“##0.00”), “ ZiB”),
     IF((A1>=POWER(2,60)),
         CONCATENATE(TEXT((A1/POWER(2,60)),“##0.00”), “ EiB”),
         IF((A1>=POWER(2,50)),
             CONCATENATE(TEXT((A1/POWER(2,50)),“##0.00”), “ PiB”),
             IF((A1>=POWER(2,40)),
                 CONCATENATE(TEXT((A1/POWER(2,40)),“##0.00”), “ TiB”),
                 IF((A1>=POWER(2,30)),
                     CONCATENATE(TEXT((A1/POWER(2,30)),“##0.00”), “ GiB”),
                     IF((A1>=POWER(2,20)),
                         CONCATENATE(TEXT((A1/POWER(2,20)),“##0.00”), “ MiB”),
                         IF((A1>=POWER(2,10)),
                             CONCATENATE(TEXT((A1/POWER(2,10)),“##0.00”), “ KiB”),
                             CONCATENATE(TEXT((A1),“##0.00”), “   B”)
                         )
                     )
                 )
             )
         )
     )
 )
)

 

© 1998-2017 – Nicola Asuni - Tecnick.com - All rights reserved.
about - disclaimer - privacy