A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Adding bytes, gigabytes, and megabytes in Excel



 
 
Thread Tools Display Modes
  #1  
Old July 2nd 07, 08:06 PM posted to microsoft.public.excel.worksheet.functions
NetTech
external usenet poster
 
Posts: 2
Default 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,
Ads
  #2  
Old July 2nd 07, 08:38 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
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,



  #3  
Old July 3rd 07, 02:34 PM posted to microsoft.public.excel.worksheet.functions
NetTech
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,

>
>
>

  #4  
Old July 3rd 07, 05:43 PM posted to microsoft.public.excel.worksheet.functions
Pete_UK
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 -



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How many bytes? GARY Excel Discussion (Misc queries) 0 April 15th 06 09:31 PM
Adding in Excel Ron Excel Discussion (Misc queries) 5 March 16th 06 01:22 AM
worksheet size in bytes Tamas Nagy Excel Discussion (Misc queries) 2 October 11th 05 04:15 PM
Add kilobytes to megabytes in the convert function Matt Thompson Excel Worksheet Functions 2 October 5th 05 04:20 PM
file 0 bytes Lyndon Baysic via OfficeKB.com Excel Discussion (Misc queries) 2 May 7th 05 12:47 PM


All times are GMT +1. The time now is 10:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.