# excel

## Excel / Calc: Convert a Hexadecimal number to Decimal

The following examples allow you to convert `hexadecimal` values of the format `0xYYYYYY` to `decimal` using a spreadsheet editor like `Calc` or `Excel`.

The following codes will remove the first two characters (the value `0x`) of the cell `B2` and then convert the result to `decimal` using the `HEX2DEC` function.

### Using the RIGHT function

In this example, we used the `RIGHT` function with the `num_chars` parameter to be equal to the number of characters in the cell minus 2. This used to delete the `0x` value from the `HEX` column by removing the first two characters of the cell.
To get the number of characters in the cell we use the `LEN` function on the cell of interest.

`=HEX2DEC(RIGHT(B2,LEN(B2)-2))`

### Using the SUBSTITUTE function

In the following example we used the `SUBSTITUTE` function to automatically find the `0x` prefix of the `HEX` value and delete it by replacing it with an empty string.

`=HEX2DEC(SUBSTITUTE(B2,"0x",""))`

### Using the REPLACE function

The last example uses the `REPLACE` function. Starting from the character in position 1 in the cell, it replaces the sub-string of size 2 with the empty string and thus deleting the prefix. Please note that this function is `not zero-based` so the first character is at position 1 and not at position 0.

`=HEX2DEC(REPLACE(B2,1,2,""))`

### Functions Legend:

• `RIGHT(text,[num_chars])``RIGHT` returns the last character or characters in a text string, based on the number of characters you specify in the variable `num_chars`. `RIGHT` always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• `LEN(text)``LEN` returns the number of characters in a text string. Again, `LEN` always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
• `HEX2DEC(number)``HEX2DEC` converts a hexadecimal number to decimal.
• `SUBSTITUTE(text, old_text, new_text, [instance_num])` – Substitutes `new_text` for `old_text` in a text string. You can use `SUBSTITUTE` when you want to replace specific text in a text string.
• `REPLACE(old_text, start_num, num_chars, new_text)``REPLACE` replaces part of a text string, based on the number of characters you specify, with a different text string. Use `REPLACE` when you want to replace any text that occurs in a specific location in a text string. `REPLACE` always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

## How to randomize order of rows in Excel

In the following video we demonstrate how to randomize the rows of an Excel sheet.

## Methodology:

• We created a new column next to the data we want to randomize their order, then we typed in the first cell the following formula `=rand()`.
`=rand()` will generate a random value between 0 and 1.
• After that we applied the same formula to the entire column.
• To apply the formula to the whole column we used a very simple method: we double-clicked on the bottom right hand corner of the cell . • Later, we sorted our date using the column of random values.
• Finally, we deleted the new column.

Alternative way to copy the formula to the entire column:

• Including the cell with the formula, select the cells in the new column that you want the new formula applied to (all the rows you want to be randomized) and the press `Ctrl+D`.

## How to “group by” and sum in Excel

In the following example we used the `Subtotal` feature to create a spreadsheet that shows the partial sum based on another column.

## Methodology:

1. Click the `Data` tab in Excel’s ribbon toolbar
2. Click the `Sort` button to sort our data by the user column
3. Click the `Subtotal` button and fill in the dialog as appropriate, then click OK

In our example, we had only two columns (`User` and `Lot`), we wanted to produce the `total sum of lots per user` and so filled the dialog as follows: What the above options do is the following:

• Use the `User` column to group on by checking when its value changes
• Use the `Sum function` on the columns that will be selected later on
• Apply the function on the `Lot` column

## Excel: A few useful functions

count() Counts all cells that contain numbers only.

counta() Counts all cells that contain any content (both numbers and text) a.k.a. are not empty.

countblank() Counts all cells that are empty.