View Single Post
  #4   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

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 -