Adding Data Values (KB, MB, GB) in Excel 2013

I am trying to add multiple values that are in the following format:

123.56 GB
54.7 GB
254 MB
65 GB
etc.

The values are in the same column, but not adjacent cells. Ideally, the result would give me the sum in GB or TB depending on the value  that the function provides.

I have researched multiple functions and VBA solutions to achieve this outcome, but nothing seems to work. The VBA just causes Excel to crash and the function throws a #VALUE error. Please help, I have been trying for awhile now.
NAHDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
This should get you very close.  Use this function in a formula.
Public Function Size(parmValue)
    Static lngPower As Long
    lngPower = Log(parmValue) \ Log(1000)
    Size = parmValue / (1000 ^ (lngPower)) & " " & Array("KB", "MB", "GB", "TB", "PB", "EB", "ZB", "YB")(lngPower - 1)
End Function

Open in new window

0
aikimarkCommented:
This has you covered through the defined sizes.
Public Function Size(ByVal parmValue)
    Static lngPower As Long
    lngPower = Int(Log(parmValue) / Log(1000))
    Size = parmValue / (1000 ^ (lngPower)) & " " & Array("KB", "MB", "GB", "TB", "PB", "EB", "ZB", "YB")(lngPower - 1)
End Function

Open in new window

0
NAHDAuthor Commented:
So just to be sure, I press alt + f11 to open VBA editor. Add a module and enter the string that you provided. Then I can use a SUM function to add the values?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
you would use the sum function against the range of cells.  Then 'wrap' that sum function result with the Size() function, passing the Sum() result as the parameter.  The Size() function merely formats the result with the "_B" suffix.

Note: You can name the function anything you want.
0
aikimarkCommented:
You could write an alternative function to pass the range (rather than a value), which would be summed and formatted.
0
aikimarkCommented:
Also note that the code assumes the parameter has a value 1000, or greater.  It will need to be tweaked if the value might be less than 1000.
0
NAHDAuthor Commented:
Thank you for your help. I was able to just covert the value into bytes and so a SUM function against all those values and convert that value back to KB, GB, TB.

Here is the custom cell format that I researched and used to convert from bytes to KB, GB, TB:

[>999999999999]0.00,,,,"TB";[>999999999]0.00,,,"GB";0.00,,"MB"

* Note: This custom cell format is not my own and I cannot remember which site that I copied it from.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
As long as you can live with the three (range) condition limit, that will work for you.  If you need to cover KB, less than 1K, or PB, you will need to use the VBA function I posted.
0
aikimarkCommented:
Note: In Excel 2007, MS introduced the Conditional Formatting Rules Manager, which does allow you to specify more than three conditions.  I find it kludgy.
ConditionalFormattingRulesMgr.jpg
0
NAHDAuthor Commented:
I knew that this was a way to get to the end result that I wanted, I just had to configure the reporting that I was doing differently to generate a value in bytes that I could pull in to Excel.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.