Lookup & Reference
Normalize storage units: binary KiB/MiB/GiB/TiB versus decimal GB
Use powers of 1024 for IEC binary units and powers of 1000 for decimal SI units; keep the two conventions distinct.
Replace nested IF unit multipliers with XMATCH exponent math
Cloud billing and disk exports mix KiB, MiB, GiB, and TiB in the same column, making direct SUM or SORT meaningless until every row shares one unit.
In the ordered scale {"KiB","MiB","GiB","TiB"}, each step is a factor of 1,024, and "GiB" sits at position 3. Raising 1024 to (Position - 3) multiplies KiB by 1024^-2, MiB by 1024^-1, GiB by 1024^0 (1), and TiB by 1024^1.
| SizeVal (A) | Unit (B) | Scale Index | 1024^(idx - 3) | Normalized GiB |
|---|---|---|---|---|
| 512 | MiB | 2 | 1 / 1024 | 0.5 |
| 2.5 | TiB | 4 | 1024 | 2560 |
| 1048576 | KiB | 1 | 1 / 1048576 | 1 |
Binary (1024) gibibyte normalization formula
Bind XMATCH with LET and check ISNA before ROUND so unrecognized unit typos flag "Unknown unit" cleanly.
=LET(idx, XMATCH(UPPER(B2), {"KiB","MiB","GiB","TiB"}), IF(ISNA(idx), "Unknown unit", ROUND(A2 * 1024^(idx - 3), 4)))Decimal SI variant: kB, MB, GB and TB
For decimal inputs, use the KB/MB/GB/TB lookup and 1000 multiplier below. Its result is GB, whereas the binary example returns GiB. Do not mix units without converting their definitions.
=LET(idx, XMATCH(UPPER(B2), {"KB","MB","GB","TB"}), IF(ISNA(idx), "Unknown unit", ROUND(A2 * 1000^(idx - 3), 4)))Inputs and interpretation
Use nonnegative byte quantities, not bits. Legacy exports sometimes misuse KB/MB labels for binary units; verify the source convention instead of guessing. The case-insensitive lookup accepts kib/mib/gib/tib for the binary example. Four-decimal output rounds very small quantities to zero; retain more precision when summing.
Try the example
Use the original values or change them to test your understanding.