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

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"))

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.

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

Select allOpen in new window

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

>>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!

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.

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

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"})

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

Select allOpen in new window

EE-Convert-B-MB.xls

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

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!

Thanks again everyone!

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

Brad