You are here: Home / Archives for Formulas

How to some times greater than 24 hours in Excel 2007

November 29, 2009 By

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/

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

Check formula errors in Excel 2007 using IFERROR Function

November 6, 2009 By

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: , , ,

Find cells that meet specific criteria in Excel 2007

September 6, 2009 By

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: , , , ,

Sum cells with multiple criteria in Excel 2007

August 25, 2009 By

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: , , ,

Count cells with multiple criteria in Excel 2007

April 18, 2009 By

In an earlier post I demonstrated How to count cells with multiple criteria in Excel 2003. I used an Array Formula together with the SUM function.

Fortunately enough in Excel 20007 we do not need that workaround. Now there is a direct function that count cells with multiple criteria: the COUNTIFS function.

Syntax

COUNTIFS(range1, criteria1,range2, criteria2…) … up to 127 range/criteria can be specified.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2), with total sales that exceed 1000. i.e. we have three criteria:

  1. “John” in column B.
  2. “Q2″ in column C.
  3. “>1000″ in column D.

We will use the following formula for this purpose:

=COUNTIFS(B2:B17;”=John”;C2:C17;”=Q2″;D2:D17;”>1000″)

This formula will give us a result of 2 as you can see.

xl7countifs

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

How to extract text from another text string

February 1, 2009 By

Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation.

Example:

Suppose that cell A1 has the text string:

“Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function:

=LEFT(A1,5)

The result is “Creat”

2. To get the last 11 characters of the string, we use the RIGHT function:
=RIGHT(A1,11)

The result is “certainties”

3. To get 7 characters from the string starting with position 10, we use the MID function:

=MID(A1,10,7)

The result is “y requi”.

4.To get the first word of the string, we use the LEFT and FIND functions:

=LEFT(A1,(FIND(” “,A1)-1))

The result is “Creativity”.

The FIND function here will give us the position of the first SPACE. We subtract 1 to get the length of the first word. That length is used as a parameter for the LEFT function.

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

How to search for a text in a formula or a comment

September 18, 2008 By

It may be interesting to know that the Find option in Excel Edit menu is not limited to finding cell values, but it can also be used to search for text in a formula or a comment.

To search for a text in a formula follow these steps.

1. In the Edit menu click Find.

2. The Find and Replace window will be shown.

3. There are three combo boxes that are labeled “Within”, “Search” and “Look in”. If those combo boxes are not shown then click on the Options command button to expand the “Find and Replace” window.

4. Click on “Look in” combo box and select Formulas.

5. Click on the Find All command button.

6. All cells that have formulas, and that contain the text searched for will be listed at the bottom of the window.
Find

Notes:

1. You can search for text in a comment in a similar way by changing your selection in the “Look in” combo box.

2. You can specify to search within one worksheet or the entire workbook, depending on your selection in the “Within” combo box..

Applies to Excel 2003

Filed Under: Data, Formulas Tagged With: , ,

How to perform autofill without dragging the fill handle

July 26, 2008 By

If you have a large worksheet with formulas and you want to perform an autofill, then it may be a tedious job to drag the fill handle across hundreds of rows or columns. Here is a simple procedure to make your life easier:

1. Select the first cell in your range (the one which has a formula already).
2. While holding down the shift key go to the last cell in the range and select it. Now you should have all your range selected.
3.In the Edit menu click Fill then Series.

Fill Series 1

4. When you get the Series window click Autofill.

Fill Series 2

All cells in the range should now be updated with formulas.

Filed Under: Data, Formulas Tagged With: , ,

How to nest and join multiple functions in a cell

June 28, 2008 By

Some Excel users, especially new comers, may think that you can only use one Excel function at a time in a cell. But the fact is that you can join as many functions as necessary in a cell. You can even nest multiple functions within each other to achieve a certain formula. Let us take some examples.

Example 1.

If you have a date in cell “A1″, you can use the Weekday function to return the day of the week like this:

=WEEKDAY(A1),

But this will only return a number. If you want the name of the day then you can nest the Weekday function within the Text() function to get the name of the day as follows:

=TEXT(WEEKDAY(A1);”ddddd”)

Example 2.

Suppose you have a list of email addresses like this, and you want to extract only website addresses from them:

User1@hotmail.com
User256@yahoo.com
User3@exceldigest.com

Two options that may come to mind is the Right() function and the Mid() function, but the Right() function will require the length of the string from the “@” sign to the end, and the Mid() function will require both the length of the string and its start position and both are not available. So here is one possible solution:

  1. Use the find() function to get the position of the “@” sign :

    2. =FIND(“@”;A1)

  2. Use the Len() function to get the length of the whole string :

    3. =LEN(A1)

  3. Nest these two functions within the Mid() function to get the final formula:

=MID(A1;(FIND(“@”;A1))+1;(LEN(A1)))

Please note that this may not be the optimum solution but it is used here to demonstrate our subject. For example instead of using the Len() function you could only use 255 (maximum string length).

Applies to: Excel 2003

Filed Under: Formulas, Functions Tagged With: ,

Autofill, formulas and cells references

April 24, 2008 By

In a previous post I explained How to use Autofill with formulas.Today I will cover the same subject but from a different perspective.

Suppose you have a list of values in a certain currency and you want to convert those values to another currency based on a given currency rate. If we follow the same scenario as in the previous post then it will go like this.

  1. Enter the formula in the first row of data as follows:

Formula Cell refrence 1

  1. Drag the fill handle across the rest of the rows to generate the formulas.

Formula Cell refrence 2

  1. Your data will now look like this.

Formula Cell refrence 3

What is wrong?

The cell reference style we are using here e.g. A3, B1 is called Relative Cell Reference. When you perform autofill downwards with relative cell reference, Excel will increase the row number by 1 successively e.g. A1, A2, A3 or B1, B2, B3 … etc.

In our case we want cell B1, which contains the rate, to be fixed. In this case we use the Absolute Cell Reference which means whatever cell reference you specify will remain fixed when you use autofill. The B1 cell will be coded like this: $B$1.

Now back to our example. This is how the formulas look after modification. $B$1 is the same in all rows.

Formula Cell refrence 4

And this is how our values will look like.

Formula Cell refrence 5

Related Links:

How to use Autofill with formulas


Applies to: Excel 2003

Filed Under: Data, Formulas Tagged With: , , , ,
« Older Posts
Newer Posts »