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

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.

 

How to hide error indicators in Excel 2007 cells

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.

Average values based on a given criteria in Excel 2007

To average a range of values that meet a given criteria or condition, you use the AVERAGEIF function.

Syntax

AVERAGEIF(range,criteria,average_range)

Range : The group of cells to be evaluated for criteria.

Criteria: is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.

Average_range: is the actual set of cells to be averaged. If omitted, range is used.

Example:

In the example below we want to average the total sales for a particular salesman (Jim). So our range is B2:B13 and our criteria is “Jim”. The range to be averaged is C2:C13. The formula will be:

=AVERAGEIF(B2:B13;”Jim”;C2:C13)

The result is 1005, i.e. (2300 + 1100 + 200 + 420) / 4

xl7average1

Notes:

1. Cells in range that contain TRUE or FALSE are ignored.

2. If a cell in average_range is an empty cell, AVERAGEIF ignores it.

3. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.

4. If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.

5. If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.

6. You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. In the previous example if you use “J*” in criteria then both “Jim” and “John” will be included in the average.

How to generate a random number within a given range

To generate a random number within a given range use the RANDBETWEEN function.

Syntax

RANDBETWEEN(bottom,top)

Bottom: is the smallest integer RANDBETWEEN will return.

Top: is the largest integer RANDBETWEEN will return.

Examples:

RANDBETWEEN (1,100) will generate numbers from 1 to 100

RANDBETWEEN (-15,2000) will generate numbers from -15 1 to 2000

Notes:

1. A new random integer number is returned every time the worksheet is calculated.

2. In Excel 2007 you can use RANDBETWEEN function directly, however in Excel 2003 (and may be earlier versions also) the function is part of the Analysis ToolPak add-in. If it is not available in your list of functions then you have to install the Analysis ToolPak. Check the following link for more help:

install and load the Analysis ToolPak add-in in Excel 2003

How to use the INDIRECT function

The INDIRECT function returns a reference specified by a text string, evaluates that reference and displays its contents. The function is useful when you want a cell reference in a formula to be variable, without the need to change the formula every time.

Syntax

INDIRECT(ref_text,a1)

ref_text: a text that contains a cell reference.
a1: a logical value that specifies what type of reference is contained in the cell ref_text.
• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. E.g. C12
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. E.g R12C3

Example 1:

indirect1

Cell B1 contains the formula: =INDIRECT(A1)
Cell A1 contains the text: “C6”
Cell C6 contains the value: 135
The INDIRECT function will evaluate the reference as: = C6, which is equal to 135.
If you change the text in cell A1 to “C3” then you will get 198 in B1.

Example 2:

indirect2

The data shown above is a snapshot from a sheet named “Sheet2″ in a workbook.

Cell B1 contains the formula: =INDIRECT(A1&”!”&”C8″)
Cell A1 contains the text: “Sheet2”
Cell C8 contains the value: 1922
The INDIRECT function will evaluate the reference as: = Sheet2!C8, which is equal to 1922.
If you change the text in cell A1 to “Sheet1” then the reference will be evaluated as: = Sheet1!C8 and you will get whatever value in Cell C8 of sheet1.

How to use the conditional sum wizard

The Conditional sum wizard helps you create complex formulas to sum values in a column based on certain criteria. The wizard is part of Excel Add-ins. You must load it first before it can show up in the Tools menu. See then bottom of this post if you want to know how to load the wizard.

The following example will demonstrate how to use the conditional sum wizard:

Suppose we have data of total sales by month and product as shown below, and we want to sum the total sales for Product3 in the first quarter (months 1 to 3).

1. On the Tools menu click Conditional Sum. The dialogue for step1 will open.

csumwizard1

2. Click on the text box then select your data range, including column headings.
3. Click next. The dialogue for step2 will open.

csumwizard2

4. Specify your conditions by manipulating the three combo boxes for Column, Is and This value. Click Add condition every time.

5. When finished with your conditions click next. The dialogue for step 3 will open.

csumwizard3

6. Specify whether you want to copy your generated formula only, or both formula and the values of your conditions.

7. Click next. The dialogue for step 4 will open.

csumwizard4

8. Specify the cell(s) where you want your generated formula (and values of conditions if applicable) to be copied.

9. Click finish. The formula will be copied to the specified cell, and the result of the formula will be shown. Here is the formula in our example:

{=SUM(IF($A$2:$A$19<4,IF($B$2:$B$19=”Product3″,$C$2:$C$19,0),0))}

How to Load the Conditional Sum Wizard

The Conditional Sum Wizard is not loaded by default. To determine whether it has been loaded, on the Tools menu, look for the Wizard menu item. If it is not present, or if when you point to it, the item Conditional Sum is not present, you need to load the add-in.

To load the Conditional Sum Wizard, follow these steps:

1. On the Tools menu, click Add-ins.
2. In the Add-Ins available list, select Conditional Sum Wizard, and then click OK.

Applies to Excel 2003

How to use the RANK function

The RANK function returns the rank of a number, i.e. its position within a list of numbers. It is based on the value of a number, relative to the other numbers in the list.
The list evaluated by the rank doesn’t have to be sorted, but to get the idea of how items are ranked imagine that the list is sorted, and then each item is given a rank depending on its position in the list.

Syntax

RANK(number,ref,order)

Number:  is the number whose rank you want to find.

Ref:  is an array of or a reference to a list of numbers. Non-numeric values in the array or the  reference are ignored.

Order: is a number specifying how to rank number.

• If order is 0 (zero) or omitted, Excel ranks number as if the list is sorted in descending order.
• If order is any nonzero value, Excel ranks number as if the list is sorted in ascending order.

Note:

The RANK function gives duplicate numbers the same rank. This however will affect the ranks of subsequent numbers. For example, if we have two numbers both have the rank of 5, then no number will have a rank of 6. The next number in the list will have a rank of 7.

Examples:

1. Rank with order 0 (descending).

rank1

This example shows ranks in descending order (Order = zero). Here are the applicable formulas in each cell:

C2: =RANK(B2,$B$2:$B$6,0)
C3: =RANK(B3,$B$2:$B$6,0)
C4: =RANK(B4,$B$2:$B$6,0)
C5: =RANK(B5,$B$2:$B$6,0)
C6: =RANK(B6,$B$2:$B$6,0)

2. Duplicate ranks.

rank2

The second example shows duplicate ranks, also in descending order (Order = zero). Notice that students 2 and 3 both have a rank of 2, and therefore rank 3 is skipped. Here are the applicable formulas in each cell:

C12:RANK(B12,$B$12:$B$16,0)
C13:=RANK(B13,$B$12:$B$16,0)
C14:=RANK(B14,$B$12:$B$16,0)
C15:=RANK(B15,$B$12:$B$16,0)
C16:=RANK(B16,$B$12:$B$16,0)

Applies to: Excel 2003

Nesting the IF function

The IF function is used to conduct conditional tests on values and formulas, and it is syntax is;

IF(logical_test,value_if_true,value_if_false)

As you can see it has only one logical test, and then the cell which has the formula will have a certain value if the test is “TRUE” and another value if the test is “FALSE”.

If you have more than one logical test, then you can nest more than one IF function within each other.

In the example below we have a list of hotel ratings and room prices. We want to check:

1. The rating of the hotel. If it is less than 4 stars then the cell value will be “NOT OK” and the test will end there.

2. If the rating is 4 stars or more then we will conduct another test on the room price.

nestedif

A generic formula for cell “C2″ for our case will be something like this:

=IF(A2<4;”NOT OK”;(Another test))

In place of “Another test” we will have the formula:

IF(B2>80;”NOT OK”;”OK”)

So joining these two formulas together we will have the final formula (in C2):

=IF(A2<4;”NOT OK”;(IF(B2>80;”NOT OK”;”OK”)))

How to use the HLOOKUP Function

The HLOOKUP function (short for Horizontal Lookup), searches for a value in the first row of a table array and returns the corresponding value in the same column from another row in the table array.

Syntax

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

  • Lookup_value:The value to search for in the first row of the table array.
  • Table_array:Two or more rows of data. The values in the first row of table_array are the values searched for the lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase texts are equivalent.
  • Row_index_num:The row number in table_array from which the corresponding value must be returned. A row_index_num of 2 returns the value in the second column in table_array; a row_index_num of 3 returns the value in the third column in table_array, and so on.
  • Range_lookup:A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If set to “FALSE”, a corresponding value will be returned only if an exact match is found. If set to “TRUE”, the nearest match will be considered if an exact one is not found.

Note:

The values in the first row of table array must be placed in ascending sort order; otherwise, HLOOKUP may not give the correct value.

Let us take a simple example to clarify the HLOOKUP usage:

In the image below, we have the worksheet: Budget with two rows of input data. Row 1 holds the quarters and row 2 the assigned budget. In the second worksheet: Spending we have the quarters listed vertically. We want to pick the budget for each quarter from the worksheet: Budget and put it in column B. In cell B2 we will enter the HLOOKUP function as follows:

=HLOOKUP(A2;Budget!$B$1:$E$2;2;FALSE)

A2 lookup value, quarter
Budget!$B$1:$E$2 Table array, found in worksheet: Budget.
2 Row_index_num, row 2 in worksheet named Budget
FALSE We want to find an exact match

Cells B3 to B5 can be updated with the HLOOKUP function by dragging the fill handle across the cells.

hlookup1

Budget

hlookup2

Spending

Applies to: Excel 2003

Random lookup based on VLOOKUP function

A few months ago I designed a monthly calendar template in Excel. One of my requirements was to pick a proverb randomly from a list and show it in the calendar. This is how I managed to do it:

I first created the list of proverbs as shown below.

xproverbs.gif

This is the calendar template

Calendar

To pick the proverb randomly I placed the VLOOKUP in the cell where I want the proverb to appear.

=VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

Now let us dissect the code (Please refer to the post on How to use the VLOOKUP Function for details):

1. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is our lookup value which generated randomly using the RAND() function, and because RAND() returns a fraction I multiplied by 10 to get a whole number. In my case I have 12 items in my table and this is why I added 3 hear to make sure that I will cover all of the table. The INT() function is used to get rid of the fraction to the right of the decimal.

2. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is where the table array is (the list of proverbs).

3. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

The 2 is the column index number. I.e. we are picking our proverb from the second column in the table array.

4. =VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)

This is the range lookup parameter. “False” means we are looking for an exact match of the lookup value.

Pleas check this post for a different approach to random lookup:

Applies to: Excel 2003