Solved

Adding Kilobytes, Megabytes and Gigabytes Together on the 1 spreadsheet

Posted on 2014-03-19
12
654 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
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 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 34

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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:itjockey
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:itjockey
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:itjockey
ID: 39941542
This Is My Question Link.

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

840 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