Home 
Search 
Today's Posts 
#1




Adding bytes, gigabytes, and megabytes in Excel
I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500
MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these as numbers and not text. I would like Excel to be able to add say 894 MB and 128 MB and come up with a total of 1 GB. Is this possible? Thanks, 
#2




Adding bytes, gigabytes, and megabytes in Excel
I set up a small table in M1:N3 with these values:
kB 1024 MB =1024*1024 GB =1024*1024*1024 and then with the following in A1:A3 : 500 MB 3 GB 200 kB I put this formula in B1 and copied it down: =VALUE(LEFT(A1,FIND(" ",A1)1))*VLOOKUP(RIGHT(A1,LEN(A1)FIND(" ",A1)),M$1:N$3,2,0) I got the following in B1:B3 : 524288000 3221225472 204800 which is the true number of bytes (note: it doesn't matter if you have Mb, MB or mB as the VLOOKUP is not case sensitive). So, applying this to your example (slightly corrected): 896 Mb 939524096 128 MB 134217728 and the sum of column B divided by N3 gives 1. Hope this helps. Pete On Jul 2, 8:06 pm, NetTech wrote: I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500 MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these as numbers and not text. I would like Excel to be able to add say 894 MB and 128 MB and come up with a total of 1 GB. Is this possible? Thanks, 
#3




Adding bytes, gigabytes, and megabytes in Excel
Thank you so much for your quick response and taking the time to answer my
question. I think you have solved the 1st part of my question, but 2nd part would be, how can I display the results in a format that is easy to read. For instance, is there a formula that displays Gb's if my results are greater than 5 gigabytes, but then displays Mb's if my results are less than (or equal to) 5 gigabytes? EXAMPLE 1 5583457484.8 (bytes)  I would like Excel to display 5.2 Gb's EXAMPLE 2 4718592000 (bytes)  I would like Excel to display 4500 Mb's "Pete_UK" wrote: I set up a small table in M1:N3 with these values: kB 1024 MB =1024*1024 GB =1024*1024*1024 and then with the following in A1:A3 : 500 MB 3 GB 200 kB I put this formula in B1 and copied it down: =VALUE(LEFT(A1,FIND(" ",A1)1))*VLOOKUP(RIGHT(A1,LEN(A1)FIND(" ",A1)),M$1:N$3,2,0) I got the following in B1:B3 : 524288000 3221225472 204800 which is the true number of bytes (note: it doesn't matter if you have Mb, MB or mB as the VLOOKUP is not case sensitive). So, applying this to your example (slightly corrected): 896 Mb 939524096 128 MB 134217728 and the sum of column B divided by N3 gives 1. Hope this helps. Pete On Jul 2, 8:06 pm, NetTech wrote: I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500 MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these as numbers and not text. I would like Excel to be able to add say 894 MB and 128 MB and come up with a total of 1 GB. Is this possible? Thanks, 
#4




Adding bytes, gigabytes, and megabytes in Excel
Assuming your value is in B17 (my test cell), you can use this formula
to get what you want: =IF(B17=5*2^30,TEXT(B17/2^30,"0.0 Gb"),TEXT(B17/2^20,"0 \Mb")) Just change the references from B17 (3 of them) to suit your data. You can copy it down if you have a number of values in the column. Hope this helps. Pete On Jul 3, 2:34 pm, NetTech wrote: Thank you so much for your quick response and taking the time to answer my question. I think you have solved the 1st part of my question, but 2nd part would be, how can I display the results in a format that is easy to read. For instance, is there a formula that displays Gb's if my results are greater than 5 gigabytes, but then displays Mb's if my results are less than (or equal to) 5 gigabytes? EXAMPLE 1 5583457484.8 (bytes)  I would like Excel to display 5.2 Gb's EXAMPLE 2 4718592000 (bytes)  I would like Excel to display 4500 Mb's "Pete_UK" wrote: I set up a small table in M1:N3 with these values: kB 1024 MB =1024*1024 GB =1024*1024*1024 and then with the following in A1:A3 : 500 MB 3 GB 200 kB I put this formula in B1 and copied it down: =VALUE(LEFT(A1,FIND(" ",A1)1))*VLOOKUP(RIGHT(A1,LEN(A1)FIND(" ",A1)),M$1:N$3,2,0) I got the following in B1:B3 : 524288000 3221225472 204800 which is the true number of bytes (note: it doesn't matter if you have Mb, MB or mB as the VLOOKUP is not case sensitive). So, applying this to your example (slightly corrected): 896 Mb 939524096 128 MB 134217728 and the sum of column B divided by N3 gives 1. Hope this helps. Pete On Jul 2, 8:06 pm, NetTech wrote: I have data in Excel that comes across as Megabytes and Gigabytes (i.e 500 MB, 3 GB, 200 BM, etc). How can I format this data so Excel recognizes these as numbers and not text. I would like Excel to be able to add say 894 MB and 128 MB and come up with a total of 1 GB. Is this possible? Thanks, Hide quoted text   Show quoted text  
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How many bytes?  Excel Discussion (Misc queries)  
Adding in Excel  Excel Discussion (Misc queries)  
worksheet size in bytes  Excel Discussion (Misc queries)  
Add kilobytes to megabytes in the convert function  Excel Worksheet Functions  
file 0 bytes  Excel Discussion (Misc queries) 