It’s my first writing in this forum, so please excuse if my suggestion is an old one…
I’m about to make a Calc Spreadsheet list of my USB Harddisks, and would like to add their sizes (Total, Used and Free) by their integer values f.ex: 98491984, but get the value presented by the standard computer units, f.ex. 94G. The new format is similar to the engineering format, that divides the number by 1000; the computer format should use 1024 as base and the standard units: B, KB, MB, … as representation.
Kind Regards, Kim
Welcome Kim!
Perhaps for your purposes it will be enough to still store the value as an integer number of bytes, so that later you can perform actions with them (for example, sum or find the average), but somewhere nearby, in the next cell, see them as an integer or double kilo-mega -terabyte?
Perhaps such a UDF will do just that?
Option Explicit
Function ConvertBytes(inputBytes As Double, Optional digitPlaces As Integer) As String
Dim sTargetFormat As String
Dim nPower As Integer
Dim aMeasurement As Variant
aMeasurement = Array("B","MiB","GiB","TiB","PiB","EiB","ZiB","YiB")
If IsMissing(digitPlaces) Then digitPlaces = 0
If digitPlaces Then
sTargetFormat = "0." & String(digitPlaces,"0")
Else
sTargetFormat = "0"
EndIf
nPower = LBound(aMeasurement)
Do While nPower < UBound(aMeasurement)
If inputBytes > 1024 Then
inputBytes = inputBytes / 1024
nPower = nPower + 1
Else
Exit Do
EndIf
Loop
ConvertBytes = Format(inputBytes,sTargetFormat) & " " & aMeasurement(nPower)
End Function
Just call it like =CONVERTBYTES(D2) or =CONVERTBYTES(D2;3)
Pay attention to the designation of units. I used IEC notation because systems based on powers of 2, however, might use binary prefixes (kibi, mebi, gibi, …) and their corresponding symbols (Ki, Mi, Gi, …) or they might use the prefixes K, M, and G, creating ambiguity
Of course, you can change them for your convenience.
Of course, you can also use the built-in function CONVERT() - just don’t forget to specify ki, Mi, Gi instead of the usual k, M, G - the calculation will be carried out correctly, with the base 1024.
2 Likes
Hi Vladyslav,
Thanks for your suggestion. I will try that to start with.
Kind Regards, Kim