Convert bytes to KB or MB automatically

[andreasvesalius]

I have a column that contains data of file sizes in bytes. I wat to convert it automatically in Kilo or Mega bytes if the number is greater than 1024 or 1024 x 1024 respectively.

 

[hatman]

=if(a1>1024*1024,a1/1024/1024&" MB",if(a1>1024,a1/1024&" KB",a1&" Bytes"))

 

[Barry Katcher]

Here's a starting point. The formula in B2 is:

=IF(A2>=1048576,A2/100000&" mb",IF(AND(A2<1048576,A2>1024),A2/1000&" kb",A2&" bytes"))

Book1
	A	B	C	D
1	Bytes	Conversion
2	500	500 bytes
3	1,020	1020 bytes
4	1,024	1024 bytes
5	50,000	50 kb
6	1,048,575	1048.575 kb
7	1,048,576	10.48576 mb
8	2,500,000	25 mb
Sheet1

 

[Barry Katcher]

Here's an amended formula that smooths it out a bit:

=IF(A2>=1048576,ROUND(A2/100000,1)&" mb",IF(A2>=1024,ROUND(A2/1000,1)&" kb",A2&" bytes"))

Book1
	A	B	C	D
1	Bytes	Conversion
2	500	500 bytes
3	1,020	1020 bytes
4	1,024	1 kb
5	50,000	50 kb
6	1,048,575	1048.6 kb
7	1,048,576	10.5 mb
8	2,500,000	25 mb
Sheet1

 

[andreasvesalius]

Thank you Barry and hatman. Excuse my ignorance, but as I'm completely new to modifying cells with formulas, how can I apply your examples to a whole column in my worksheet?

 

[Drew]

Another possible way... custom formatting:

With "Bytes" as a tag..
[>=1048576]#,##0.0,,_k"MB";[>=1024]#0.0,_m"KB";#.0_m"Bytes"

Without "Bytes"
[>=1048576]#,##0.0,,_k"MB";[>=1024]#0.0,_m"KB";General


Copy the format you want to apply, highlight your range of interest, select format/cells/number tab/custom. Below the word "Type:" (white box) paste the format there then hit ok.

 

[Barry Katcher]

Place the formula in a cell in the same row, to the right of the first cell with a file size and change the reference to refer to that cell. Then, just copy down as far as needed. In my example, I placed the formula in cell B2 and referred it to cell A2, and copied down.

 

[andreasvesalius]

Thank you, Drew! Your example just worked fine!
Can any of you please explain how can I apply the formulas in the previous examples to a whole (B) column?

 

[andreasvesalius]

Thank you, Barry! Without your clear explanations I would have never made it! I tried to insert your example in the formula bar and nothing happened. Now I can appreciate your examples.
Thank you all for your kind help!

 

[andreasvesalius]

Here is my "modification", that imitates Windows file size report more closely:

=IF(B1>=1048576,ROUND(B1/1048576,2)&" MB",IF(B1>=1024,ROUND(B1/1024,2)&" KB",B1&" bytes"))