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