Convert Used Storage Values to GB in Boring Reports

Our Boring camera report currently presents Used Space, the storage consumed by each camera, in a variety of units (ie: KB, MB, GB, TB).  We have it in our backlog to update the report but, in the meantime, here is a little Excel trick to normalize the values to GB so that you can sort them accordingly.

*Note:  The new reports in v4 are normalized to GB by default.

How to use this formula

• Run the camera report in Boring Toolbox
• Open the report in Excel
• Create a column right next to the current "Used Space" column in the report
• Name the column, "Used Space (GB)" - This can be anything you would like
• Paste the formula below starting in row 2
  • This formula will convert MB, TB and KB to GB
  • In the example spreadsheet the "Used Space" column is L
    • If your column is a different letter you will need to replace the letter L with the correct column name.  
    • The column identifier in the formula is highlighted in red just in case you need to update it with a different ID.
• Once you have verified that the conversion is correct, copy the formula all the way down the column
• Now you can sort your cameras by Used Space in ascending or descending order

=IF(ISNUMBER(SEARCH("MB",L2)),(VALUE(SUBSTITUTE(L2," MB",""))*0.001),IF(ISNUMBER(SEARCH("TB",L2)),(VALUE(SUBSTITUTE(L2," TB",""))*1000),IF(ISNUMBER(SEARCH("GB",L2)),(VALUE(SUBSTITUTE(L2," GB",""))),IF(ISNUMBER(SEARCH("KB",L2)),(VALUE(SUBSTITUTE(L2," KB",""))*0.000001),""))))

What is the formula actually doing?

This formula is a series of nested IF statements that

• Search for a unit abbreviation in the Used Space cell (ie: MB)
• It will delete the unit abbreviation text (ie: MB) and extract only the value of the number (ie: 1000)
• Then depending on the abbreviation will multiply that extracted value to convert to GB (ie: 1000MB = 1GB)

Here is an example:

Used Space cell shows 134.5 MB

• The formula will see that it has MB in the cell and will execute the following formula
  • VALUE(SUBSTITUTE(L2," MB",""))*0.001
• VALUE(SUBSTITUTE(L2," MB","")) applied to "134.5 MB" will do the following
  • Take " MB" and replace it with "" (nothing) and then will convert the text into a number so that we can perform an operation on it.  In this case the outcome would be 134.5. 
• It then multiples 134.5 * .001 because there are 1000MB in 1GB
• Yielding .1345 GB