# Excel Question - Standardize Units Of Measurement

6Okay so not sure if anyone here is an Excel Ninja or not but figured I’d try.

I run a report on our network. I wind up with a report of about 50 sites showing their bandwidth, usage, etc.

Problem is, our sites are all over the place and this system kicks out measurements in everything from bps, kbps, Mbps and Gbps.

I’m wanting to know if there is a relatively easy way to standardize all these values to Mbps since there are a few manual calculations that I run and I’m sick of having to manually change like 60 values to get them all to Mbps so that I can just copy/paste this into my template and get all my calculations run correctly.

I’d really, REALLY prefer to avoid having to run VB scripts since this is a report that gets shared around and people freak out when they get a sheet that wants to run macros.

Any ideas?

- 8 comments, 16 replies
- Comment

@Bingo I don’t see how you’d do it without VBA.

I think the best thing to do would be to open a new sheet and code up the VBA module, then save that out as an Excel Add-In. That way you can run the script on your report but keep the report itself free of any VBA or macros when you send it out.

I’m sure you thought of this, but have you made sure there’s no way to force your reporting tool to output everything in bps?

Otherwise… you’re going to get into some nasty formulae.

Assuming that your source cell is A1, that your source is of the format ‘number[space]unit’, that you’re dealing with bps, kbps, mbps, and gbps only (case insensitive), you’re looking at something like this (to return mbps):

`=IF(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))="bps",NUMBERVALUE(LEFT(A1,LEN(A1)-SEARCH(" ",A1,1)))/1000000,IF(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))="Kbps",NUMBERVALUE(LEFT(A1,LEN(A1)-SEARCH(" ",A1,1)))/1000,IF(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))="Gbps",NUMBERVALUE(LEFT(A1,LEN(A1)-SEARCH(" ",A1,1)))*1000,NUMBERVALUE(LEFT(A1,LEN(A1)-SEARCH(" ",A1,1))))))`

(note, the above should copy/paste alright, but visually it’s rough - there should be a space inside of every set of quotation marks)…basically, a ton of conditionals that split the string into the two relevant bits. Excel formulae don’t really support very complicated conditionals, so in order to get the equivalent of an

`else`

statement, you keep nesting`IF`

s. There’s a limit, I don’t remember what it is. This example tests for bps, kbps, gbps, and then I got lazy so it just assumes mbps otherwise. You’d use this to generate a new column, and hide the existing one (or replace the values after generating it once).It could also be done using

`VLOOKUP`

or, possibly, a`CHOOSE`

function… These would require use of reference data in the table (so, one (likely hidden) column of unit and one of conversion rate) and you’d still have to do the string splitting.`CHOOSE`

can be hacked into a sort of`case`

conditional, but I don’t really think it would clean things up much.It should be doable w/o VBA, just… ugly and no fun to maintain.

@brhfl Can’t this be achieved in Excel 2016 using the IFS function? You’ll still have multiple formulae calculating the value, but at least it’s not the god-awful nested IFs. I’d quickly test to see if I’m going to sound like an idiot, but I don’t have 2016 on this laptop.

Somebody check for an IFS solution! Further simplification if you can do multiple changes to the sheet/database.

You could skip the right/left value by first splitting the reported value of “10mbps” into “10” and “mbps” columns or however (using whatever formula necessary to compensate for how the data looks), and have a third column check the units column with the IFS function, and depending on the value, multiply or divide ‘10’ accordingly.

It’s also been a really long day so I apologize if this solution is unnecessarily convoluted or just plain confusing or wrong.

@brhfl DAMN, I’m glad I’m retired and don’t have to produce stuff like that any more. Very fine work. I am bowing before your wisdom and skill.

@brhfl This is almost ok…except it’s 1024 per multiplication right? Also you could just look in the value for “G”, “K”, “M” and use a base of “BPS” otherwise.

@tightwad I think for throughput it’s 1000.

@zehnn Good to know, I’ll file that away for when my organization gets Office 2016 in 2021.

@brhfl @zehnn @tightwad Thank you! This is seriously epic. I’m going to be trying this today after I finish up a current project I’m working.

@brhfl Yep. Given the restrictions this seems best. While it is unweildy, it’s not bad, and maintenance isn’t difficult.

Make a table with units and multipliers. Do a vlookup and multiply.

Get @toposhaba2 to do it for you.

Is your data reported with a space like “270 bps” or is it “270bps”. Either way this formula will return the value multiplied by the appropriate multiplier…I went base of “bps” but it would probably be better to base it in mbps or whatever the expected value is. You would have to adjust the formula a bit…assuming values starting in A1, enter this formula in say B2 and copy down as needed

=IF(ISNUMBER(TRIM(LEFT(A1,LEN(A1)-3))+0),TRIM(LEFT(A1,LEN(A1)-3)),TRIM(LEFT(A1,LEN(A1)-4))*(IFERROR(–(FIND(“m”,A1)>0)*1000,0)+IFERROR(–(FIND(“k”,A1)>0)*1000000,0)+IFERROR(–(FIND(“g”,A1)>0)*1000000000,0)))+0&“bps”

@tightwad Worth noting that

`FIND`

is case sensitive whereas`SEARCH`

is not, so if OP’s reports properly capitalize ‘M’ and ‘G’, that would need to be reflected.@brhfl @tightwad Thank you both! I had forgotten that FIND is case sensitive so missed that at first. I’m going to give this a shot.

@brhfl Good catch! Love crowd sourced solutions…

How does the spread sheet look? Do you do this every day, weekly, or monthly? How many entries are there per report and how often do you run it?

@Kevin I do it monthly, once a month. I have 45 networks I get traffic information on so it’s 45 rows long x 10 columns wide.

@Bingo if each network is in the same row every month, couldn’t you put in a multiplier or divider for each row?

Like, what’s ever in this cell, divide by 1024?

I’m pretty good with Excel but need to see a sample of the sheet. Can you provide a pic of sample data?

@densa @Bingo @brhfl @tightwad @zehnn You might want to share a Google doc. https://www.google.com/docs/about/

I’m still fond of having it as a VBA module saved as an Add-In.

Just in case you want to go that route. This assumes the current numbers are in Column A starting at Row 1, change accordingly. It will over-right the existing numbers with “TheNewValue Mbps”.

Also not case sensitive so bits and bytes are treated the same (“Gbps” and “GBps” both seen as “Gbps”)

`Sub ConvertByteTags()`

`Dim iRow As Integer`

`Dim strValue As String`

`Dim strSizeType As String`

`Dim dblNewValue As Double`

`Const ConversionFactor As Integer = 1000`

`iRow = 1`

`Do`

`strValue = Cells(iRow, 1).Value`

`If strValue = "" Then`

`Exit Do`

`End If`

`strSizeType = Right(strValue, 4)`

`Select Case UCase(strSizeType)`

`Case "TBPS"`

`dblNewValue = Val(strValue) * ConversionFactor * ConversionFactor`

`Case "GBPS"`

`dblNewValue = Val(strValue) * ConversionFactor`

`Case "MBPS"`

`dblNewValue = Val(strValue) * 1`

`Case "KBPS"`

`dblNewValue = Val(strValue) / ConversionFactor`

`Case Else`

`dblNewValue = Val(strValue) / ConversionFactor / ConversionFactor`

`End Select`

`Cells(iRow, 1).Value = dblNewValue & " Mbps"`

`iRow = iRow + 1`

`Loop`

`End Sub`

@Ignorant OVERWRITE you ignorant piece of shit.

I have nothing of value to add.

I am jealous of all you people that know Excel. It has been something that I have wanted to learn for years.

@conandlibrarian