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