MB GB KB excel report format 1

[Murugs]

Hello

Please help me. I have the following report in excel

total used avail
972MB 2252KB 970MB
51MB 7504KB 43MB
200GB 143MB 199GB
0MB 118MB 0MB

I need to convert all the values into GB and sum it up. How do I do this ?

 

[SkipVought]

Hi,

Have you Googled conversions?

 

[Murugs]

I know the conversions

972MB 2252KB 970MB

this should result like

.972 .002 .970

But how do I search for the word MB and divide by 1000 in excel.
972/1000 = 0.972
KB = 2252 = 2252/1000000 = 0.002

 

[LyleU]

Murugs,

Close but no cigar. Most people use a conversion factor of 1000 for convenience, but the actual factor is 1024.

8 Bits = 1 Byte
1 Kilobyte = 1,024 Bytes
1 Megabyte = 1,048,576 Bytes
1 Gigabyte = 1,073,741,824 Bytes
1 Terabyte = 1,099,511,627,776 Bytes





Lyle
----
"For every expert, there is an equal and opposite expert." - Arthur C. Clarke.

 

[jonsi]

to achieve what you want I've had to split your data

Assume that your data as you posted it is in E13:G16 and that you have a table of conversions in E8:F10
Col E Col F
GB = 1
MB = 1000
KB = 1000000

Firstly, separate your numbers from text
=LOOKUP(9.9E+307,--MID(E13,MIN(FIND({1,2,3,4,5,6,7,8,9,0},E13&1234567890)),ROW(INDIRECT("1:"&LEN(E13))))
This gives you just the numbers. My output was to I13:K16
Copy this formula into the adjacent cells

Secondly, determine what you are converting; GB, MB or KB
=RIGHT(E13,2)
This gives you MB or KB etc. My output was to M1316
Copy this formula into the adjacent cells

Finally, convert your output into GB
=IF(M13="GB",(I13/$F$8),(IF(M13="MB",(I13/$F$9),(IF(M13="KB",(I13/$F$10))))))
This divides the number output and divides it by the values in the table E8:F10. I output to Q13:S16
Copy this formula into the adjacent cells

I did upload a file called murugs gb mb kb.xlsx
Jonsi

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[strongm]

>but the actual factor is 1024

As a point of pedantry, technically, under the International System of Units, the factor is indeed 1000. The binary version is called the mebibit, the unit symbol for which is Mibit (or Mi)

 

[jonsi]

>but the actual factor is 1024

As a point of pedantry, technically, under the International System of Units, the factor is indeed 1000. The binary version is called the mebibit, the unit symbol for which is Mibit (or Mi)

which is why the conversion factors live in cells and those cells are absolute referenced in the formulae rather than fixed values. Either 1000 can be used or 1024 etc.

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[strongm]

My comment was aimed at LyleU's post, not yours.

 

[jonsi]

not a criticism strongm absolutely no offence taken or even considered

...your post prompted me that I'd forgotten to mention that the conversion factors were changeable and that the result was based on them

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[Murugs]

Thanks Jonsi. Your excel file worked. This is what I needed. But in many cells I get "REF"..
I uploaded the file here -

http://www.filedropper.com/murugsgbmbkb1

can you check please

Muru