Excel farmulas

How to get the day of the week as text in Excel 2010
january 4, 2013 by admin
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: excel 2007, excel 2010, excel formulas, excel functions
How to change the range of a defined name in Excel 2010
december 5, 2012 by admin
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: excel 2007, excel 2010, formulas
How to define named ranges in Excel 2010
november 25, 2012 by admin
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: excel 2007, excel 2010, formulas
How to calculate age in Excel 2010
november 16, 2012 by admin
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 t