Skip to content

Instantly share code, notes, and snippets.

@seb26

seb26/A_one_line_formula.md

Last active March 1, 2026 18:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save seb26/e3837b36ddb5c4b945e3ef40c4e48b19 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save seb26/e3837b36ddb5c4b945e3ef40c4e48b19 to your computer and use it in GitHub Desktop.
Download ZIP
This formula can be used in Google Sheets to take a value in bytes and then represent it in a human-readable format with binary units (KiB, MiB, GiB, and so on).
Raw
A_one_line_formula.md 
=ROUND( A2/(1024)^(FLOOR(log(A2)/log(1024))), 2) & " " & SWITCH( FLOOR( log(A2) / log(1024) ) ,0,"Bytes",1,"KiB",2,"MiB",3,"GiB",4,"TiB")
Raw
B_about.md

This formula can be used in Google Sheets to take a value in bytes and then represent it in a human-readable format with binary units (KiB, MiB, GiB, and so on).

If your sheet contains data concerning file sizes, systems and other information storage, then representing it in binary units (or IEC units) is absolutely essential and the most correct format for that use case.

If you represent your Byte amounts in decimal format (KB, MB, etc), the values are arguably incorrect in the context of these systems. A lot of confusion stems from using the letters 'KB' to represent binary units which should be denominated as 'KiB' instead. See https://en.wikipedia.org/wiki/Binary_prefix#Inconsistent_use_of_units for more background on this topic.

Example

Example table showing differences between the two numbering systems.

Bytes Decimal Binary
1024 1.02 KB 1.00 KiB
1,048,576 1.05 MB 1.00 MiB
4,194,300 4.19 MB 4.00 MiB
1,153,156,984 1.15 GB 1.07 GiB

Scripts available on the Internet

There are a number of scripts, formulas and snippets available on StackOverflow, Google discussion forums and other sites, but the majority of them represent data sizes in decimal instead of binary.

Decimal units

Binary units

Raw
C_example_results_table.md
Bytes Formula: =A2/(1024)^(FLOOR(log(A2)/log(1024))) FLOOR(log(A2)/log(1024) =SWITCH( FLOOR( log(A2) / log(1024) ) ,0,"Bytes",1,"KiB",2,"MiB",3,"GiB",4,"TiB") All in one All in one rounded to 2 decimal
512 512 0 Bytes 512 Bytes 512 Bytes
1024 1 1 KiB 1 KiB 1 KiB
2048 2 1 KiB 2 KiB 2 KiB
4096 4 1 KiB 4 KiB 4 KiB
1,048,576 1 2 MiB 1 MiB 1 MiB
36,068,183 34.39729977 2 MiB 34.3972997665405 MiB 34.4 MiB
148,503,136 141.6236267 2 MiB 141.623626708984 MiB 141.62 MiB
1,377,849,741 1.283222568 3 GiB 1.28322256822139 GiB 1.28 GiB
4,242,623,877 3.951251392 3 GiB 3.95125139225274 GiB 3.95 GiB
24,935,584,458 23.22307272 3 GiB 23.2230727169663 GiB 23.22 GiB
27,298,486,746 25.42369696 3 GiB 25.4236969593912 GiB 25.42 GiB
27,298,486,746 25.42369696 3 GiB 25.4236969593912 GiB 25.42 GiB
@nebirhos
Copy link

nebirhos commented Sep 24, 2020

Awesome, thank you!

@cglosser
Copy link

cglosser commented Apr 28, 2021

Super useful! This doesn't accommodate negative sizes (perhaps for a file that has shrunk). Changing to

=ROUND( A2/(1024)^(FLOOR(log(ABS(A2))/log(1024))), 2) & " " & SWITCH( FLOOR( log(ABS(A2)) / log(1024) ) ,0,"Bytes",1,"KiB",2,"MiB",3,"GiB",4,"TiB")

(throwing ABS into the logarithms) takes care of that.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment