5

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Just a user
  • 609
  • 2
  • 10
  • 19
  • Are the units in a separate column or the same? – xQbert Jul 03 '14 at 14:05
  • 1
    @xQbert The units are in the same column :-) I can alter this if necessary. – Just a user Jul 03 '14 at 14:09
  • @Bartdude I have no programming experience in Excel, hence the question here :-) Sorry guys, excel noob with a deadline :P – Just a user Jul 03 '14 at 14:10
  • 2
    http://stackoverflow.com/q/1533811/2258 shows a similar question related to formatting. – Richard Morgan Jul 03 '14 at 14:23
  • @pnuts > Thanks for pointing this out. That said, apparently it is also acceptable for OP to show no efforts in this specific case. indeed, the same kind of question in any other programming language would have been closed very quickly... – Laurent S. Jul 03 '14 at 15:22
  • Yay.. Downvote something just because you think the question is stupid. Downvote to you for thinking ur better then others and giving unconstructive input. – Just a user Jul 03 '14 at 15:49
  • @RemyvanTour > I don't think your question is stupid and never said anything so. But I'm sure it is against SO principle that OP should show minimal research effort in their questions. As SO is a crowd-moderated site, it's up to everybody to downvote any question that would need it. As soon as I'm the only one downvoting it, there's no problem : your question will stay on the site and answer will help others. But still I don't see any effort from your side, and your reaction comforts my downvote. – Laurent S. Jul 03 '14 at 16:15

3 Answers3

4

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)))}
Danielle
  • 317
  • 2
  • 10
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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 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 Jul 03 '14 at 14:24
  • 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. – JPG Jul 03 '14 at 14:42
  • Worked like a charm after setting the Value types in a different Column and setting another sumif for Bytes, thanks! :-) – Just a user Jul 03 '14 at 14:58
3

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

Hubbitus
  • 5,161
  • 3
  • 41
  • 47