View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK Pete_UK is offline
external usenet poster
 
Posts: 8,856
Default 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,