Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Adding Kilobytes, Megabytes and Gigabytes Together on the 1 spreadsheet

Posted on 2014-03-19
12
Medium Priority
?
802 Views
Last Modified: 2014-03-19
Heyas,

If have spreadsheet with values like this in 1 particular column.

50MB
60 KB
6.1 GB
60KB (Yes Spacing does vary)
223MB

How do I do add these entries together to get the answer in MB. I tried looking into the convert function but I don't think it gives me what I need.

Thank you.
0
Comment
Question by:Zack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 39941486
Try something like

=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1000^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G"},0)))/1000000
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39941490
Convert everything to a common unit (maybe bytes) into a new column, then add them.

HTH,
Dan
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39941495
Function DemoFunction() As Double

    Dim cel As Range
    Dim dblGB As Double, dblMB As Double, dblKB As Double

    For Each cel In Range("D1:A5").Cells
        Select Case Right(cel, 2)
            Case Is = "GB"
                dblGB = dblGB + Left(cel, Len(cel) - 2)
            Case Is = "MB"
                dblMB = dblMB + Left(cel, Len(cel) - 2)
            Case Is = "KB"
                dblKB = dblKB + Left(cel, Len(cel) - 2)
        End Select
    Next cel
    
    DemoFunction = (dblGB * 1024) + (dblKB / 1024) + dblMB
    
End Function

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39941499
Here is a more accurate version of the SUMPRODUCT formula:

=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1024^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G","T"},0)))/1024^3

And a more accurate UDF:

Public Function SumStorage(ByVal Source As Range) As Double

    Dim Cell As Range
    Dim Result As Double
    Dim Position As Long
   
    For Each Cell In Source.Cells
        Position = 0
        Do: Position = Position + 1: Loop Until Mid(Cell.Value, Position, 1) Like "[A-Za-z ]"
        Select Case True
            Case InStr(UCase(Cell.Value), "TB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024 * 1024 * 1024 * 1024
            Case InStr(UCase(Cell.Value), "GB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024 * 1024 * 1024
            Case InStr(UCase(Cell.Value), "MB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024 * 1024
            Case InStr(UCase(Cell.Value), "KB") > 0: Result = Result + CDbl(Left(Cell.Value, Position - 1)) * 1024
        End Select
    Next Cell
    SumStorage = Result

End Function

To use the UDF, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Use this formula:

=SumStorage(A1:A5)

The formula and UDF return KB. To return TB:

=SumStorage(A1:A5)/1024^3

Kevin
0
 

Author Closing Comment

by:Zack
ID: 39941500
Thanks for the lighting response that why I love experts exchange.

http://www.rondebruin.nl/
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39941501
Is this MVP Ron?
0
 

Author Comment

by:Zack
ID: 39941509
No it isn't :( that was meant to be messaged to a friend of mine who want some examples on excel lists.

My apologies.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39941515
No worries. I was a little surprised because Ron (the Microsoft Excel MVP) is rather capable of solving such a riddle on his own ;-)
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39941517
Mr. zorvek (Kelvin Jones),

Surfing EE & I show your solution. will you please Explain the formula? if you have bit of spare time.
=SUMPRODUCT(LEFT(A1:A5,LEN(A1:A5)-2)*1024^(MATCH(LEFT(RIGHT(A1:A5,2)),{"K","M","G","T"},0)))/1024^3

Thanks
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39941522
A few things.

My name is Kevin, not Kelvin.

It is not considered good etiquette to hijack another OP's question.

I will leave you with a valuable tip: plug the formula in and use Excel's Evaluate Formula function to see how it works ;-)

If you want more I suggest asking your own question.

Kevin
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39941532
Opps Typo mistake Sorry,

I will leave you with a valuable tip: plug the formula in and use Excel's Evaluate Formula function to see how it works ;-)


Thumbs Up


Thank You
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39941542
This Is My Question Link.

Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

636 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