Calculation with values expressed in different units (KB, MB, GB)

23,531

Solution 1

Assuming the list is in range A1 to A4

{=SUM(VALUE(LEFT(A1:A4,FIND(" ",A1:A4)))*IF(RIGHT(A1:A4,2)="MB",1/1000,IF(RIGHT(A1:A4,2)="KB",1/1000000,1)))}

Solution 2

You can use elegant trick with 10 power by Dave Bruns:

=LEFT(A1,LEN(A1)-2)/10^((MATCH(RIGHT(A1,2),{"PB","TB","GB","MB","KB"},0)-3)*3)

If you are using Excel on Mac then you need to replace "," by ";" (thanks to @pokkie):

=LEFT(A1;LEN(A1)-2)/10^((MATCH(RIGHT(A1;2);{"PB";"TB";"GB";"MB";"KB"};0)-3)*3)

You will also have to search and replace any values such as 1.2GB with 1,2GB

Solution 3

Text to Columns with space as the delimiter and:

=SUMIF(B:B,"GB",A:A)+SUMIF(B:B,"MB",A:A)/1000+SUMIF(B:B,"KB",A:A)/1000000

may suit.

Share:
23,531
Remy van Tour
Author by

Remy van Tour

Your friendly IT guy from the Netherlands.

Updated on August 21, 2020

Comments

  • Remy van Tour
    Remy van Tour about 3 years

    I am looking for a simple way in Excel to calculate with units of different values. I have a list of several values in KB, MB and GB and I want to get a calculated output in GB.

    Here's a piece of the list:

    66.0 MB
    570 KB
    1.10 GB
    2.21 KB
    

    Output: ??? GB.

    All values are in the same row (C) with B/KB/MB/GB behind the value.

  • JPG
    JPG over 9 years
    It should be 1024 instead of 1000 and 1024/1024 instead of 1000000 for correct conversion between GB/MB/KB. Besides my Excel requires ';' as argument separator (could be different for other Excel versions though)
  • JPG
    JPG over 9 years
    So it depends on definition used. Didn't know that, always thought GB/MB/KB conversion was done based on 2 to the n-th power.
  • Remy van Tour
    Remy van Tour over 9 years
    Worked like a charm after creating an extra (IF) for Bytes. Thanks! :D
  • Remy van Tour
    Remy van Tour over 9 years
    Worked like a charm after setting the Value types in a different Column and setting another sumif for Bytes, thanks! :-)
  • Green
    Green about 5 years
    I find yours the most general and elegant answer- up-voted!