We help IT Professionals succeed at work.

How do display number as KB, MB, or GB?

greddin
greddin asked
on
I have a column that is a list of numbers. The numbers are in Bytes. I would like to display them as either KB, MB, or GB depending on size.

Sample numbers are:
1176104854  (would be displayed as 1.09 GB)
739342040 (705 MB)
108700 (106 KB)

Can someone help me out with a formula?

Thank you.
Comment
Watch Question

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Something like this?
If A1 contains your number

=IF(A1>1024*1024*1024,ROUND(A1/1024/1024/1024,3)&" GB", IF(A1>1024*1024, ROUND(A1/1024/1024,3)&" MB", A1/1024&" KB"))
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Or this one to get exactly

1.xx for GB (2 dp)
no decimals for MB
no decimals for KB

=IF(A1>1024*1024*1024,TRUNC(A1/1024/1024/1024,2)&" GB", IF(A1>1024*1024, ROUND(A1/1024/1024,0)&" MB", ROUND(A1/1024,0)&" KB"))

Notice I use TRUNC for GB instead of ROUND, because rounding gives me 1.10, but your result is 1.09 so I assume you want 1.095 to be chopped off at 1.09??
A VBA function idea

i.e.
A1=1176104854
ConvertByte(A1)=1.09 GB

I'm sure its not the most efficient way, but its simple and quick
Function ConvertByte(ByVal Value As Range) As String
Dim UOM As Variant
Dim i As Long
ConvertByte = Value.Value
UOM = Array("B", "KB", "GB", "TB", "PB", "EB", "ZB", "YB")
Do While CDbl(ConvertByte) >= 1024
  ConvertByte = ConvertByte / 1024
  i = i + 1
Loop
ConvertByte = Application.WorksheetFunction.RoundDown(ConvertByte, 2) & " " & Choose(i + 1, "B", "KB", "MB", "GB", "TB", "PB", "EB", "ZB", "YB")
End Function

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You might consider a formula like one of:
=LOOKUP(1,A1/1024^{4,3,2,1},TEXT(A1/1024^{3,2,1},"[<10]#.00 ;# ") &{"GB","MB","KB"})
=LOOKUP(1,A1/1024^{4,3,2,1,0},TEXT(TRUNC(A1/1024^{3,2,1,0},2),"[<10]#.00 ;# ") &{"GB","MB","KB","B"})

The first formula rounds up or down (rather than truncating). It returns #N/A if the value is less than 1 KB.
The second formula truncates and returns a value in Bytes if less than 1 KB.

Brad
Very elegant Brad.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Brad,

Just curious..

I'm intrigued that your formula has no real rules around where to round/trunc.
For something that comes out 1095 x 10^x (4 significant digits), it gives 11 in one case, but 1.09 in another.
Data          CK	Brad1	Brad2
11761048540   10.95 GB	11 GB	11 GB
1176104854    1.09 GB	1.09 GB	1.09 GB
117610485.4   112 MB	112 MB	112 MB
739342040     705 MB	705 MB	705 MB
108700        106 KB	106 KB	106 KB
0             0 KB	#N/A	#N/A
123           0 KB	123 B	123 B

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I challenged myself to develop a more compact formula. My first attempt displayed 2 decimal places everywhere, but that didn't seem to match greddin's expected results. That's why I used a conditional number format in the TEXT function.
The resulting formula displays two decimal places for numbers less than 10, and 0 decimal places otherwise.

With many questions on Experts Exchange, potential solutions are easy if you understand the exact problem. When they are posted somewhat ambiguously (as in this instance), then you make assumptions that may turn out incorrect.

Brad
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
>>The resulting formula displays two decimal places for numbers less than 10, and 0 decimal places otherwise.

When you put it that way it makes perfect sense. Very reasonable rule.
Well done!

Author

Commented:
This morning I was surprised to see so many responses to my question. This is great! I'm trying out these now.

I have a question about byundt's solution. I've put that in as a formula but I get either TRUE or FALSE displayed from it. What am I doing wrong?

Thanks.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
How exactly did you enter the formula?

If you did it like shown below, then I can understand the TRUE / FALSE results:
=C1=LOOKUP(1,A3/1024^{4,3,2,1},TEXT(A3/1024^{3,2,1},"[<10]#.00 ;# ") &{"GB","MB","KB"})

Note the beginning of the formula. It creates a Boolean expression asking whether cell C1 contains something like 103 GB as its value. If so, the result is TRUE. If not, then it is FALSE.

Brad

Author

Commented:
Hi Brad:

I've entered it in as this: (J2 is the first cell containing 1176104854). This gives me false

=LOOKUP(1,J2/1024^{4,3,2,1},TEXT(J2/1024^{3,2,1},"[<10]#.00 ;# ") &{"GB","MB","KB"})
=LOOKUP(1,J2/1024^{4,3,2,1,0},TEXT(TRUNC(J2/1024^{3,2,1,0},2),"[<10]#.00 ;# ") &{"GB","MB","KB","B"})
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I've attached a sample workbook showing the correct result with your sample data.

If you could post a sample workbook showing the TRUE / FALSE results, that would be helpful. All I need to see is the little bit returning the wrong answers.

Brad
KB-MB-GB-Q26249039.xls
greddin,

I'm not sure how that returns a false, as it returns a division of J2's Value

Is the number formatting as General?

Below is a summary of the returned values atm with the spreadsheet.
                CK		CK		Bromy2004	byundt		byundt
		32958167	32958178	32958368	32958569	32958569
Data					
11761048540	10.953 GB	10.95 GB	10.95 GB	11 GB		11 GB
1176104854	1.095 GB	1.09 GB		1.09 GB		1.10 GB		1.09 GB
117610485.4	112.162 MB	112 MB		112.16 MB	112 MB		112 MB
739342040	705.092 MB	705 MB		705.09 MB	705 MB		705 MB
108700		106.15234375 KB	106 KB		106.15 KB	106 KB		106 KB
0		0 KB		0 KB		0 B		#N/A		#N/A
123		0.1201171875 KB	0 KB		123 B		#N/A		123 B

Open in new window

EE-Convert-B-MB.xls

Author

Commented:
Brad, attached is a small sample of what I have.
experts-exchange-sample.xls
greddin,
You had the formula twice... It's corrected.. See the attachment

Brad, GREAT formula :-)
experts-exchange-sample.xls
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
You put two formulas in a single cell. Excel therefore treated it as a Boolean expression, hence the TRUE and FALSE results.

I modified your workbook to put formulas in two columns. I also modified the first formula to return answers in Bytes for the few small files.
=LOOKUP(1,J2/1024^{4,3,2,1,0},TEXT(J2/1024^{3,2,1,0},"[<10]#.00 ;# ") &{"GB","MB","KB","B"})

Brad
experts-exchange-sampleQ26249039.xls

Author

Commented:
Ah, I'm embarrassed now :) Sorry, I took your code above literally and pasted it in twice. I should have caught that!

Thanks for yours and everyone else's help!

Author

Commented:
Thanks again everyone!
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Gregory,
Thanks for the kind words, grade and a very interesting problem.

Brad