Excel Digest

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.

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.

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.

The IF function, one of many EXCEL logical functions, is used to evaluate a logical condition, passed as an argument to the function.  The function will then return one of two values specified as the second argument or the third argument in the function. It will return the second argument if the test evaluates to TRUE or the third argument if the test evaluates to FALSE.

Syntax

IF(logical test , value if TRUE, value if FALSE)

Logical test:  is a condition that evaluates to TRUE or FALSE.

Value if TRUE : is the value to be returned by the function if  logical test evaluates to TRUE.

Value if FALSE : is the value to be returned by the function if  logical test evaluates to FALSE.

Examples:

A1 = 6

B1 = 10

=IF(A1>5; “Valid value”; “Invalid Value”): returns “Valid value”

=IF(A1>B1;A1-B1;B1-A1) : returns 4. This function will always return a positive number.

To install and load the Analysis Toolpak in Excel 2007 follow these steps:

1. Click the Office button.
2. Click Excel Options, the Excel Options window will open.



3. In the left pane of Excel Options click Add-Ins, The right pane will show a list of Active and Inactive  Add-ins.
4. At the bottom of the right pane there is a combo box (drop-down) labeled Manage. Select Excel Add-Ins from the drop-down then click GO. The Add-Ins window will open.



5. In the Add-Ins window select the check box that says: Analysis ToolPak, then click OK.

To hide rows based on cell value in Excel 2007 follow these steps:

1. Prepare your data including a header row.
2. In the Home tab, Editing group, click Sort and Filter then select Filter from the menu.
3. Your data will now look like this:

In this example we want to hide rows where the sales are greater than or equal to 2000. I.e. we will only show rows where the sales are less than 2000.

1. Click on the filter arrow next to Sales.
2. Point to Number Filters and select Less Than.



3. The Custom AutoFilter window will open.



4. Enter 2000 in the Combo box to the right and click OK.
5. Now all rows with values greater than or equal to 2000 will be hidden. Here is the final result:

Note:

If your data is text then you will see Text Filters instead of Number Filters in the menu.

To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :

A1 = 02:30

A2 = 10:15

A3 has the formula: =SUM(A1:A2)

then A3 will have the correct values of 12:45.

However if your total times are greater than 24 hours then things get tricky.

If you have :

A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.

To retain the hours beyond 24, you have two alternatives:

1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)

2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.

In both cases the formula will remain as it is. Only the cell format differs.

See another example below:

More posts on time:

http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/

If you want to copy data from Excel to Microsoft word then follow these steps:

In  Excel

1. Select the worksheet data (range) that you want to copy to a Word document.
2. On the Home tab, Clipboard group, click

One of the newly introduced functions in Excel 2007 is the IFERROR Function. If you are using a formula in a cell, this function allows you to trap formula errors and specify a value to be substituted in the cell in case of an error.

Syntax

IFERROR(value,value_if_error)

Value is the argument (formula) that is checked for an error.

Value_if_error is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Example:

I have:

1. Numeric values in cells A1, A2 and B2.
2. In C2 I have the following formula:

=B2/A2*$A$1

If cell A2 has a value of 0 (zero) the formula will return #DIV/0. To trap this error in Excel 2003 you will use a formula like this:

=IF(ISNUMBER(B2/A2*$A$1);B2/A2*$A$1;0)

The ISNUMBER function, part of the first argument to the IF function; will decide if the value returned from our formula is a number. In this case it will accept the result; otherwise it will substitute a 0. Notice that our formula is supplied twice: as part of the first argument to the IF function, and as a second argument.

The IFERROR function is much simpler.

=IFERROR(B2/A2*$A$1;0)

In this example if there is no error the result of the formula: B2/A2*$A$1 will be returned and accepted, otherwise a 0 will be substituted.

To copy data and then paste it as a picture (image) follow these steps:

1. Select the range you want to copy.
2. In the Home tab, Clipboard group, click Copy.
3. Select a cell in the range where you want to paste the picture.
4. In the Home tab, Clipboard group, click on the arrow below the Paste icon, point to As Picture and click Paste Picture Link.