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.
Comments
-
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
andGB
and I want to get a calculated output inGB
.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 over 9 yearsIt 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 over 9 yearsSo 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 over 9 yearsWorked like a charm after creating an extra (IF) for Bytes. Thanks! :D
-
Remy van Tour over 9 yearsWorked like a charm after setting the Value types in a different Column and setting another sumif for Bytes, thanks! :-)
-
Green about 5 yearsI find yours the most general and elegant answer- up-voted!