Menu
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.
798
Select the cell(s) of interest, then click Format > Cells > Number tab, select Custom in the Category pane, and enter said custom format in the narrow Type field to the right. Click OK to exit the Format Cells dialog and you should have what you want, at least where your example is concerned.
1 KB (kilobyte) is 1,024 bytes. 1 MB (megabyte) is 1,0242 bytes. 1 GB (gigabyte) is 1,0243 bytes. 1 TB (terabyte) is 1,0244 bytes.
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)))}
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.
33
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
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
