Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-08-09
10
Medium Priority
?
7,427 Views
Last Modified: 2014-08-16
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.
0
Comment
Question by:NAHD
  • 7
  • 3
10 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 40251858
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40251958
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
 

Author Comment

by:NAHD
ID: 40252205
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 46

Expert Comment

by:aikimark
ID: 40252225
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40252231
You could write an alternative function to pass the range (rather than a value), which would be summed and formatted.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 40252252
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
 

Accepted Solution

by:
NAHD earned 0 total points
ID: 40253012
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40253105
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40253649
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
 

Author Closing Comment

by:NAHD
ID: 40264837
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question