Excel Question - Standardize Units Of Measurement

Bingo thought this was worth mentioning said Mon, Aug 8th 2016 at 1:01pm ET

Okay 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

Ignorant said Mon, Aug 8th 2016 at 2:10pm ET:

@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.

3
- Reply
- Whisper
brhfl said Mon, Aug 8th 2016 at 2:22pm ET:

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 IFs. 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.

10
- Reply
- Whisper
- @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.
  
  zehnn said Tue, Aug 9th 2016 at 12:13am ET
  - 1
  - Reply
  - Whisper
- @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.
  
  OldCatLady said Tue, Aug 9th 2016 at 12:15am ET
  - 5
  - Reply
  - Whisper
- @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 said Tue, Aug 9th 2016 at 12:31am ET
  - 2
  - Reply
  - Whisper
- @tightwad I think for throughput it’s 1000.
  
  Ignorant said Tue, Aug 9th 2016 at 12:38am ET
  - 2
  - Reply
  - Whisper
- @zehnn Good to know, I’ll file that away for when my organization gets Office 2016 in 2021.
  
  brhfl said Tue, Aug 9th 2016 at 8:39am ET
  - 1
  - Reply
  - Whisper
- @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.
  
  Bingo said Tue, Aug 9th 2016 at 10:58am ET
  - 1
  - Reply
  - Whisper
- @brhfl Yep. Given the restrictions this seems best. While it is unweildy, it’s not bad, and maintenance isn’t difficult.
  
  stienman said Tue, Aug 9th 2016 at 3:28pm ET
  - 0
  - Reply
  - Whisper
toposhaba2 said Mon, Aug 8th 2016 at 7:11pm ET:

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

4
- Reply
- Whisper
- Get @toposhaba2 to do it for you.
  
  ACraigL said Tue, Aug 9th 2016 at 10:59am ET
  - 2
  - Reply
  - Whisper
tightwad said Tue, Aug 9th 2016 at 1:29am ET:

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”

2
- Reply
- Whisper
- @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 said Tue, Aug 9th 2016 at 8:59am ET
  - 2
  - Reply
  - Whisper
- @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.
  
  Bingo said Tue, Aug 9th 2016 at 10:56am ET
  - 2
  - Reply
  - Whisper
- @brhfl Good catch! Love crowd sourced solutions…
  
  tightwad said Tue, Aug 9th 2016 at 9:16pm ET
  - 2
  - Reply
  - Whisper
Kevin said Tue, Aug 9th 2016 at 9:18am ET:

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?

1
- Reply
- Whisper
- @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 said Tue, Aug 9th 2016 at 10:55am ET
  - 1
  - Reply
  - Whisper
- @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?
  
  Kevin said Tue, Aug 9th 2016 at 12:12pm ET
  - 0
  - Reply
  - Whisper
densa said Tue, Aug 9th 2016 at 10:26am ET:

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

1
- Reply
- Whisper
- @densa @Bingo @brhfl @tightwad @zehnn You might want to share a Google doc. https://www.google.com/docs/about/
  
  OldCatLady said Tue, Aug 9th 2016 at 12:14pm ET
  - 2
  - Reply
  - Whisper
Ignorant said Tue, Aug 9th 2016 at 11:56am ET:

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

1
- Reply
- Whisper
- @Ignorant OVERWRITE you ignorant piece of shit.
  
  Ignorant said Tue, Aug 9th 2016 at 6:03pm ET
  - 1
  - Reply
  - Whisper
ConAndLibrarian said Tue, Aug 9th 2016 at 12:41pm ET:

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.

3
- Reply
- Whisper
- @conandlibrarian
  
  daveinwarsh said Tue, Aug 9th 2016 at 3:21pm ET
  - 3
  - Reply
  - Whisper