How to convert numbers between decimal and hexadecimal

To convert numbers between decimal and hexadecimal you use the equivalent Excel built-in function.

To convert from decimal to hexadecimal use the function DEC2HEX.

Syntax

DEC2HEX(number;places)

Number: The decimal number you want to convert into hexadecimal.

Places: Optional, the number of characters or positions to use. You can use this option to pad the return value with leading zeros. If number is negative, then the places parameter is ignored.

Examples:

1. A1 = 35; B1 = DEC2HEX(A1;4).

The returned vale in B1 will be 0023. Notice the leading zeros.

2. A1 = 1234; B1 = DEC2HEX(A1).

The returned vale in B1 will be 4D2.

To convert from hexadecimal to decimal use the function HEX2DEC.

Syntax

HEX2DEC (number)

Number: The hexadecimal number you want to convert into decimal. It cannot contain more than 10 characters.

 

Examples:

1. A1 = 100; B1 = HEX2DEC(A1).

The returned value in B1 will be 256.

2. A1 = FF25; B1 = HEX2DEC(A1).

The returned value in B1 will be 65317.

3. A1 = FFFFFFF6CC; B1 = HEX2DEC(A1).

The returned value in B1 will be -2356 (negative number).

 

Use INDEX and MATCH functions for table lookup

 

The table above lists some internet Top Level Domains (TLD) and their equivalent country codes.

How can we look up the country name from the table if we know its TLD? E.g. we have the code “cn” and we want to know the corresponding country name.

We can achieve this by using the INDEX and MATCH functions to search for a specific TLD code and pick up the corresponding country name from the adjacent column,  as follows.

The INDEX function, syntax:   INDEX(array;row_no, column_no). Find details here:

http://www.exceldigest.com/myblog/2008/07/16/how-to-use-the-index-function/

It takes three arguments:  array or table, row no. and column no. and returns the value at the intersection of the row and column.

In this example if we put: =INDEX(A2:B9;4;2). I.e. Table A2:B9, row 4 (of the table, not the row of the worksheet) and column 2, then we will get the country “China”. However we don’t have row no. but we have the TLD code. This is where we need the MATCH function.

The MATCH function, syntax:  MATCH(lookup_value;array;match_type). Find details here:

http://www.exceldigest.com/myblog/2008/07/03/how-to-use-the-match-function/

It takes three arguments: lookup value, lookup array and match type and returns the position where the value is found (row no. within the table). So we can use the MATCH function to get the row no. and then feed it into the INDEX function:

=MATCH(“cn”;A2:A9;0). This function will return a row no. of 4.

We can join the two functions together as follows:

=INDEX(A2:B9;MATCH(“cn”;A2:A9;0);2). This will return the country “China” as we have seen before.

Note: you can replace “cn” with the address of the cell which has the TLD code.

How to get the day of the week in Excel 2010

To get the day of the week as a number you use Excel WEEKDAY function.

Syntax

WEEKDAY(serial_number,[return_type])

Serial number: a serial number that represent the date.

Return type (optional): a number that determines how the weekday is calculated. In other words, it specifies what is the first day of the week and whether numbering will start from 0 or will start from 1.

Here is a list of the valid return types:

1 or omitted       Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Excel.

2             Numbers 1 (Monday) through 7 (Sunday).

3             Numbers 0 (Monday) through 6 (Sunday).

11           Numbers 1 (Monday) through 7 (Sunday).

12           Numbers 1 (Tuesday) through 7 (Monday).

13           Numbers 1 (Wednesday) through 7 (Tuesday).

14           Numbers 1 (Thursday) through 7 (Wednesday).

15           Numbers 1 (Friday) through 7 (Thursday).

16           Numbers 1 (Saturday) through 7 (Friday).

17           Numbers 1 (Sunday) through 7 (Saturday).

 

Examples:

 

Cell A1 have the date: 25/11/2012, which is Sunday.

 

  1. Cell B1 have the formula: =WEEKDAY(A1;1).  The value in B1 will be 1.
  2. Cell B1 have the formula: =WEEKDAY(A1;2).  The value in B1 will be 7.
  3. Cell B1 have the formula: =WEEKDAY(A1;16).  The value in B1 will be 2.
  4. Cell B1 have the formula: =WEEKDAY(A1;17).  The value in B1 will be 1.

 

Excel 2010: How to access worksheet built-in functions in VBA

VBA has got it is own set of functions which you can use in your code. You can extend this set of functions by using EXCEL built-in functions from within VBA. These functions will have the same syntax and parameters as in EXCEL itself. Here are some examples:

1. Sum a range of cells and put the result in VBA variable “Total”.

Total = WorksheetFunction.Sum(Range(“A1:A10”))

2. Count a range of cells and put the result in VBA variable “Count”.

Count = WorksheetFunction.Count(Range(“A1:A10”))

3. Find the maximum value within a range of cells and put the result in VBA variable “Max”.

Max = WorksheetFunction.Max(Range(“A1:A10”))

4. Use the Vlookup function to look for a value and return its corresponding match.

LookupVal = WorksheetFunction.VLookup(“G”, Range(“A1:B10”), 2, False)

Note:

Vlookup will return a run time error if the lookup value is not found. To avoid this, enter the following statement somewhere before the VLookup statement:

On Error Resume Next

This way you will not have an error message and no value will be returned from the function.

One more example. The code shown below checks to see if cell “B15” is numeric. If it is numeric, it will convert its value to hex and put the result in “C15”. If not it will give an error message.

Set myRange = Worksheets(“Sheet1”).Range(“B15”)
If WorksheetFunction.IsNumber(myRange) Then
c = WorksheetFunction.Dec2Hex(myRange)
Worksheets(“Sheet1”).Range(“C15”).Value = c
Else
MsgBox “Invalid decimal value”
End If

How to join text from multiple cells into one string

If you have multiple cells that contain text and you want to join or merge them in one cell then you can use the concatenate function.

Syntax:

CONCATENATE(text1, text2, text3 ……)

You can concatenate up to 255 items into one text string.

Example:

Cell A1 = “MBA”

Cell B1 = “Master of Business Administration

Cell C1 contains the formula: CONCATENATE(A1; B1)

The value of C1 will be “MBAMaster of Business Administration”

You can also use literals (fixed text) in the CONCATENATE function. E.g. if C1 has the formula:

CONCATENATE(A1; “ – “; B1

Then the value of C1 will be “MBA – Master of Business Administration”

You can also use the ampersand (&) calculation operator to achieve the same result as the CONCATENATE function. The last example will look like this:

=A1 & ” – ” & B1

 

How to create a user-defined function in Excel 2007 or Excel 2010

If you know Visual Basic programming then you can create your own user-defined functions in Excel 2007 or Excel 2010. Follow the steps below:

1.      Click on Excel Developer tab. If you can’t see the developer click Here.


2. On the Code group click Visual Basic.

3. You will be switched to Visual Basic Editor Menu.

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

6. Type the Visual Basic code for your function.

7. On the File menu click Close and return to Microsoft Excel.

8. Your new function should now be ready for use like any other Excel function.

How to sum non-adjacent cells

Suppose you have a rang of cells, say A1:A20, and you want to sum only cells A1, A4 , A6, A7and A8 which are non-adjacent. You can write a formula like this:

=A1+A4+A6+A7+A8

This is OK for a few cells but for large range, it is better to write the formula as follows:

=SUM(A1,A4,A6:A8)

Which means some A1, A4 and the range A6 to A8.

EXCEL logical functions : the NOT function

The NOT function, one of many EXCEL logical functions, is used to evaluate a single logical condition, passed as an argument to the function.  It will return the opposite of that argument. I.e.:

  1. If the evaluated logical condition is TRUE, the function will return FALSE.
  2. If the evaluated logical condition is FALSE the function will return TRUE.

Syntax

NOT(logical)

Logical is a condition that evaluates to TRUE or FALSE.

Examples:

A1 = 6

B1 = 10

=NOT(A1>5) : returns FALSE.

= NOT(A1>10): returns TRUE.

=NOT(A1=B1): returns TRUE.

You can nest the NOT function within another logical function as follows:

=IF(NOT(A1=B1) ;”Valid numbers”;”Invalid numbers”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value: “Valid numbers”, because the NOT function will evaluate to TRUE.

EXCEL logical functions : the OR function

The OR function , one of many EXCEL logical functions, is used to evaluate a number of logical conditions, passed as arguments to  the function.  The function will return a single value of TRUE or FALSE.

Syntax

OR(logical1, logical2 ………)

logical1 and logical2 are conditions that evaluate to TRUE or FALSE. The first argument is required the second one is optional.

You can have up to 30 arguments in EXCEL 2003 and 255 in EXCEL 2007.

Examples:

A1 = 6

B1 = 10

=OR(A1>5;B1>10) : returns TRUE.

= OR(A1>10;B1>10):returns FALSE.

=OR(A1>5;B1>5): returns TRUE.

You can nest the OR function within another logical function as follows:

=IF(OR(A1>5;B1>10) ;”Valid numbers”;”Invalid numbers”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value : “Valid numbers”, because the OR function will evaluate to TRUE.

EXCEL logical functions : the AND function

The AND function , one of many EXCEL logical functions, is used to evaluate a number of logical conditions, passed as arguments to  the function.  The function will return a single value of TRUE or FALSE.

Syntax

AND(logical1, logical2 ………)

logical1 and logical2 are conditions that evaluate to TRUE or FALSE. The first argument is required the second one is optional.

You can have up to 30 arguments in EXCEL 2003 and 255 in EXCEL 2007.

Examples:

Cell A1 = 6

Cell B1 = 10

Cell C1 :

=AND(A1>5;B1>10) : returns FALSE.

=AND(A1>5;B1=10): returns TRUE.

=AND(A1>5;B1>5): returns TRUE.

You can nest the AND function within another logical function as follows:

=IF(AND(A1>5;B1>10) ;”Valid Range”;”Invalid Range”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value : “Invalid Range”, because the AND function will evaluate to FALSE.