Go Back

Normalize size units to Gigabytes

Read time: 30 minutes

Having trouble using Excel? You want to normalize size units to Gigabytes but do not know how to? Then, here’s the complete guide to your problem.

Normalize size units to Gigabytes

Formula

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

Explanation

Here is the explanation that how we can normalize size units to Gigabytes.

Example 1

If you want to normalize size units to Gigabytes while using Excel, you can apply this formula based on MATCH, LEFT, and RIGHT functions.

 

Figure 1: Normalize size units to Gigabytes in Excel by the simple formula

 

In mentioned above example, the formula is:

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

Important note

The assumption of this formula is that the last 2 characters include:
A number
A unit of measure

Yes, both will include for the last 2 characters.
This simple formula cleverly completes the mission without any long-term usage of functions. If you are thinking that, why this formula works the best? This is because digital units have a power of 10 relationship that helps to separate the number part from the unit. Then it divides the number with exact divisor to normalize size units to Gigabytes. As we are calculating thereby the power of 10, our new formula would become:

=number/10^power

Getting the exact number, the formula with no units from the left up would be:

=LEFT(B5,LEN(B5)-2)

If you want to get the power, the formula would become:

=MATCH(RIGHT(B5,2),{"PB","TB","GB","MB","KB"},0)

By using this formula one can get the position of the unit in an array constant.

Example 2

Let’s take another example, that for the formula in C5, the unit we have is KB which gives us the position 5. We can adjust this result by subtracting 3 and then multiplying it by 3. It will give us the power of 6 which is the exponent that will calculate the gigabytes correctly.

=900/10^6
=900/1000000
=0.0009

So, this is how one can easily normalize size units to gigabytes in excel as a pro.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need help to figure out how to normalize all my data to a value of 100
Solved by C. S. in 22 mins
ok here is what i'm trying to do.... I'm working on an inventory database. cell D2 has a drop down with my unit size, if it's a 14 or 12oz unit than i want to divide cell G2, which is total number of units by the number of units that come on a pallet so that i can get my true pallet count. this is the best i can do.... =if(D2="14oz","12oz",G2/120,IF(D2={"32oz"},G2/130)) Thanks!
Solved by Z. B. in 15 mins
I want to calculate the number of units that are required to be purchased with a minimum $100 purchase. But the minimum purchase needs to be rounded to be multiples of the number of units that are sold in a package. For example The Unit price is $60 per 1,000 pieces or $0.06 each. The package size is 2500 pieces. So I need to calculate how many packages I have to buy in order to exceed $100 and that enables me to ship full packages. In this case, I know the answer is one package - which would be $150 (clearly over the $100 minimum). How would I write an Excel formula in order to calculate 80,000 different types of units?
Solved by M. Y. in 26 mins

Leave a Comment

avatar