You are here: Home / Archives for Formulas

How to get the day of the week as text in Excel 2010

January 4, 2013 By

Excel WEEKDAY function will return the day of the week as a number. See details here:

How-to-get-the-day-of-the-week-in-excel-2010

To return the day of the week as text (name of the day), we combine the WEEKDAY function with the TEXT function as follows:

TEXT(WEEKDAY(A1;2);”ddd”) will give the abbreviated name of the day : Sun , Mon, Tue … etc.

TEXT(WEEKDAY(A1;2);”dddd”) will give the full name of the day : Sunday , Monday … etc.

 

Examples:

 

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

 

1. Cell C1 have the formula : =TEXT(WEEKDAY(A1;1);”ddd”). The value in C1 will be “Sun”

2. Cell C1 have the formula : =TEXT(WEEKDAY(A1;17);”ddd”). The value in C1 will be “Sun”

3. Cell C1 have the formula : =TEXT(WEEKDAY(A1;2);”ddd”). The value in C1 will be “Sat”

Why the returned day of the week is “Sat” instead of “Sun” in example No. 3 above? This is because the TEXT function will always assume 1 as Sunday, 2 as Monday and so on. This is only compatible with WEEKDAY return type of 1 and 17. So you have to be careful if you are using a return type other than these two. You have to use some arithmetic to get the correct name of the day in this case.

 

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

How to change the range of a defined name in Excel 2010

December 5, 2012 By

In a previous post I explained how to define named ranges in Excel 2010.

OK, you have defined a name for a specified range. Later on you decided to change the size of that range either by expanding it or reducing it. Here is how you do it:

1. In the Formulas tab, Defined Names group click Name Manager. The Name Manager window will open. It will list all your defined names.

2. Select the name you want to change its range then click Edit. The Edit Name window will open.

3. Click in the Refers To edit box. Now you can enter the new range through the keyboard, or you can highlight your range using the mouse and EXCEL will update the range for you automatically.

4. Click OK.

 

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

How to define named ranges in Excel 2010

November 25, 2012 By

If you have a range of cells that hold a specific type of data e.g. Sales, No. of students etc. then you can give that range a meaningful name. Later if you want to refer to the range you can do that by specifying its name instead of using its cell reference.

In the example below we have two columns, Month (column A) and Sales Value (column B).

If we want to sum the total sales for the 12 months then we will use this formula:

=sum(B2:B13)

Alternatively we can give the range B2:B13 a name like this “SalesValue” and then use the sum formula as follows:

=sum(SalesValue)

The names will make it easier for you to refer to a range and to make your formulas more meaningful.

How do you define a named range?

1. Select the range that you want to name.

2. In the Formulas tab, Defined Names group click Define Name. The New Name window will open.

3. In the Name field enter the name you want to give to your range.

4. In the Scope combo box, if you want this name to be known to other sheets in the workbook, select Workbook. If the name will only be used in the current worksheet, then select the name of the worksheet.

5. In the Refers To field make sure that the required range is specified.

6. Click OK when you are done. Your defined name is now ready to be used in any formula.

 

 

 

 

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

How to calculate age in Excel 2010

November 16, 2012 By

To calculate a person (or anything else) age you basically subtract his date of birth from today’s date. However which date functions to use depends on the amount of precision and the output format you want.

Suppose that we want to calculate the age of someone who is born on 15/06/1973 and we have his birth date in cell A1.

1. If we want his age in whole number years only, e.g. 40 years, then we can use this formula:

=YEAR(TODAY())-YEAR(A1)

2. If we want his age in whole numbers and a fraction of a year, e.g. 40.5 years, then we can use this formula:

=(TODAY()-A1)/365.25

3. If we want his age in years and months, then we can use a formula like this one:

=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”

The output of this formula will be as follows:

40 years and 2 months.

4. If we want to extend the previous formula to include days, then we can use a complex formula like this one:

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375;1)*30.4375) & ” days”

The output of this formula will be as follows:

40 years, 2 months and 18 days.

This formula could yield a one day difference due to approxmation.

What if you want to calculate the age in a target date other than today’s date?

Well, simply put the target date in another cell and replace the function TODAY() with the reference of the target cell. In the previous example if we want to calculate the age in 31 July 1995, then we can put 31/7/1995 in cell B1 and modify the first formula to be like this:

=YEAR(B1)-YEAR(A1)

Note:

Excel does not recognize dates before year 1900 (windows) or 1904(Macintosh). So formulas given above are also subject to Excel limitations.

 

 

 

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

How to show or hide formulas in Excel 2010

March 30, 2012 By

You can show or hide formulas in Excel 2010 in tow ways:

A. The quick (shortcut) way:

  • Press the control key and the ~ key at the same time (CTRL ~) to show formulas in the cells.
  • Press the same keys again to hide formulas in the cells.

B. Using Excel Options:

  1. Click File.
  2. Click Options. Excel Options window will open.
  3. Click on the Advanced tab.
  4. Go to the section titled “Display options for this worksheet”.
  5. Select the check box that says “Show formulas in cells instead of their calculated results”.

Filed Under: Excel 2010, Formulas Tagged With: ,

How to join text from multiple cells into one string

October 25, 2011 By

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

 

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

How to calculate a running total in a column

June 2, 2011 By

Suppose you have a journal in which you register daily sales, and you want to know the accumulated sales each day.

We can do this in Excel as follows:

Column A:  Date entry.

Column B: Sales entry.

Column C: A formula to calculate the running total

  1. In C2 enter the same value as B2. This is the first row of sales.
  2. In C3 enter the formula : =C2+B3.
  3. Select cell C3 and drag the fill handle down until the end of your data.
  4. Now C4 will have the formula C3+B4, C5 will be C4+B5 and so on. The running total will be updated automatically.

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

How to sum non-adjacent cells

January 19, 2011 By

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.

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

EXCEL logical functions : the NOT function

January 21, 2010 By

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 logical functions : the OR function

January 7, 2010 By

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: , ,
« Older Posts