Forumla to add Hard Drive sizes

[Skyler_Proctor]

I am keeping track of multiple hard drives and the amount of data in GB/TB on them, is it possible to for Airtable to have a formula to add computer data, aka GB/TB? So like if I had a drive that had 900GB and one that had 450GB they would add to 1.35TB?

Thank you in advance!

Skyler

[Mac]

@Skyler_Proctor you could use a formula to handle the conversion from total GB of your harddrives to TB.

As a bonus, if you’d like the field value to display the units next to the total you can use the concatenate formula as well.

[Justin_Barrett]

@Mac: I think that @Skyler_Proctor is aware that it can be done. He’s probably hoping that someone will assist with the doing.

@Skyler_Proctor: You’re not gonna believe this, but I just discovered that Airtable already has some of this functionality built in.

To begin my test, I typed “135 MB” into a single line text field. (Why MB for a drive size and not GB? No idea. Just bear with me.) In the next field, I planned on writing a formula to convert the numerical part into bytes before working out the addition of a second drive. My assumption was that using the VALUE function would strip off the “MB”, leaving only the number, so I tested the assumption with this formula:

VALUE({Size 1})

I was expecting 135, but what I got was 141557760. At first I thought the “MB” had somehow converted into some bizarre numerical value that screwed up the formula, but on a whim, I made another formula field where I multiplied 135 * 1024 * 1024, which output the same number.

Long story short: Airtable will automatically convert a value expressed in KB, MB, GB, or TB into the proper byte count. All you need to do is run it through a formula that uses the VALUE function on the text. Doesn’t matter if there’s a space between the number and the size identifier or not, it still works.

So now the only thing left is to add the byte counts, then convert back to the proper size identifier depending on that value. I could’ve done it in a single formula, but felt that it’s more efficient to use two: one to determine the label (TB, GB, MB, KB, or none), and another one to do the math based on that.

Here’s my test table:

Notice the last two records. Many assume that the different levels of size are simple multiples of 1000, so 500GB doubled must be 1TB. However, they’re actually multiples of 1024. That takes me back to the first record, which is based on your original comment, and which shows that the true sum of 900GB and 450GB is 1.32TB, not 1.35TB. Gotta love computer math! :slightly_smiling_face:

Here are the formulas. First for {Total Bytes}:

VALUE({Size 1}) + VALUE({Size 2}) `

Next for {Label}:

IF(
    {Total Bytes} >= POWER(1024, 4),
    "TB",
    IF(
        {Total Bytes} >= POWER(1024, 3),
        "GB",
        IF(
            {Total Bytes} >= POWER(1024, 2),
            "MB",
            IF(
                {Total Bytes} >= 1024, "KB"
            )
        )
    )
)

And finally, {Final sum}:

ROUND(
    {Total Bytes} / POWER(
        1024,
        SWITCH(
            Label,
            "TB", 4,
            "GB", 3,
            "MB", 2,
            "KB", 1,
            0
        )
    ),
    2
) & Label