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

excel excel-formula excel-2013 array-formulas
Remy van Tour picture Remy van Tour · Jul 3, 2014 · Viewed 21.1k times · Source

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.

Answer

Danielle picture Danielle · Jul 3, 2014

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)))}

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Excel: Searching for multiple terms in a cell

I use this handy equation to search for a term inside of a cell in excel. =IF(ISNUMBER(SEARCH("*Gingrich*",C1)),"1","") This equation searches for the presence of Gingrich in C1, if it exists, it displays a 1. All I'd like …

Read More
Separate octets of IP address using formulas

I want to separate out the octets of an IP address using Formulas. I have tried some things like substitute & find but cannot figure this out. Example of what I want to achieve, by only starting with Cell A1 …

Read More