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