Solved

Adding Kilobytes, Megabytes and Gigabytes Together on the 1 spreadsheet

Posted on 2014-03-19
12
608 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 26

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now