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