You are here: Home / Formulas

EXCEL logical functions : the NOT function

January 21, 2010 By admin Leave a Comment

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.

Filed Under: Excel 2007, Formulas, Functions Tagged With: Excel, Excel 2007, Formulas, Functions

EXCEL logical functions : the OR function

January 7, 2010 By admin Leave a Comment

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.

Filed Under: Excel 2007, Formulas, Functions Tagged With: Excel 2007, Formulas, Functions

EXCEL logical functions : the AND function

January 5, 2010 By admin Leave a Comment

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.

Filed Under: Excel 2007, Formulas, Functions Tagged With: Excel 2007, Formulas, Functions

EXCEL logical functions : IF function

January 2, 2010 By admin 2 Comments

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.

Filed Under: Excel 2007, Formulas, Functions Tagged With: Excel, Excel 2007, Formulas, Functions

How to some times greater than 24 hours in Excel 2007

November 29, 2009 By admin 6 Comments

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:

XL7SumHrs

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/

Filed Under: Date and Time, Excel 2007, Formatting, Formulas, Functions Tagged With: Excel 2007, Excel help, Formatting, Formulas, Functions

Check formula errors in Excel 2007 using IFERROR Function

November 6, 2009 By admin Leave a Comment

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.

Filed Under: Data, Excel 2007, Formulas, Functions Tagged With: Excel 2007, Excel help, Formulas, Functions

How to hide error indicators in Excel 2007 cells

October 28, 2009 By admin Leave a Comment

I find it annoying sometimes when I enter a correct formula in a cell and Excel gives me an error like “Inconsistent Formula” or “Formula Omits Adjacent Cells”.

XL7Error

Fortunately you can hide these error indicators through Excel Options.

  1. Click the Microsoft Office button. XL7Office
  2. Click Excel Options.
  3. In the left pane of Excel Options click Formulas.
  4. In the  Error Checking section, clear the Enable background error checking check box.
  5. Click OK.

Filed Under: Excel 2007, Formulas, Functions Tagged With: Excel 2007, Excel Formulas, Excel Functions, Excel help

Find cells that meet specific criteria in Excel 2007

September 6, 2009 By admin Leave a Comment

To find cells that meet a specific criteria, for example cells with conditional formatting or cells which have data validation, proceed as follows:

  1. In the Home tab Editing group click Find and Select.

    2. xl7find

  2. Click on:
    1. Formulas : to find cells that contain formulas.
    2. Comments : to find cells that contain comments.
    3. Conditional Formatting : to find cells that have Conditional Formatting.
    4. Constants : to find cells that contain constants.
    5. Data Validation : to find cells that have data validation.
  3. More options are available if you click Go To Special. Then you will get the Go To Special dialogue where you can specify specific criteria for the cells to be found and selected.

Note:

You can search the entire sheet for the specified criteria or you can limit your search by selecting a range of cells.

Filed Under: Excel 2007, Formatting, Formulas, General, Validation Tagged With: Excel 2007, Excel help, Formatting, Formulas, Validation

Sum cells with multiple criteria in Excel 2007

August 25, 2009 By admin 2 Comments

In an earlier post I demonstrated How to sum cells with multiple criteria in Excel 2003. I used an Array Formula consisting of the SUM function and the IF function.

In Excel 2007 however a new function is introduced that facilitates sum with multiple criteria: the SUMIFS function.

Syntax

SUMIFS (sum_range, criteria_range1, criteria1, criteria range2, criteria2…) … up to 127 range/criteria can be specified.

sum_range : Required. One or more cells to sum.

criteria_range1: Required. The first range in which to evaluate the associated criteria.

criteria1: Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added.

criteria_range2, criteria2, … : Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

In the example below we want to sum the sales for “John” in quarter 2 (Q2). I.e. we have two criteria:

1. “John” in column B.
2. “Q2″ in column C.

We will use the following formula for this purpose:

=SUMIFS(D2:D17;B2:B17;”John”;C2:C17;”Q2″)

Only rows 3, 10 and 14 will qualify for this formula (yellow color) and it will give us a result of 9547 as you can see.

xl7sumifs


Filed Under: Excel 2007, Formulas, Functions Tagged With: Excel 2007, Excel help, Formulas, Functions

How to calculate a running average in a column

August 7, 2009 By admin Leave a Comment

To calculate a running average, or a moving average, for a range of cells (a column), you use the AVERAGE function. The only trick you need to apply is to make your range changing continuously.

In the example below we want to calculate the running average of the sales from January to June.

Our ranges for the months will be as follows:

January – one cell : B2:B2
February- 2 cells B2:B3
March – 3 cell2: B2:B4 … and so on.

The first cell of the range is always the same for all months : B2, so we will make it absolute reference like this: $B$2. Therefore our formulas , in C column will be like this:

January: =AVERAGE($B$2:B2)
February: =AVERAGE($B$2:B3)
March: =AVERAGE($B$2:B4) …. Ans so on.

Enter the first formula in cell C2 and autofill down the rest of the range.

running-average1

Data

running-average2

Formulas

Filed Under: Data, Excel 2007, Formulas, Functions Tagged With: Excel 2007, Excel Formulas, Excel help, Functions
« Older Posts