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

## Comments

0 comments

Please sign in to leave a comment.