Rambling Librarian’s Posterous Postings

Ivan Chew, Singapore (RamblingLibrarian.blogspot.com) 



July 29, 2008

Functions: IF

From Excel help, the syntax is:
IF(logical_test,value_if_true,value_if_false)

My understanding:
  • logical_test = whatever I want to test/ check/ compare
  • value_if_true = the text/ number I wish to display if the what I wish to check, from above, is TRUE
  • value_if_false = the text/ number I wish to display if the logic test is otherwise

What's cool (and also somewhat complicated when I encountered the first time) is the nested IF functions.

Like this example from the Excel help:
If AverageScore is:
Greater than 89 -> A
From 80 to 89 -> B
From 70 to 79 -> C
From 60 to 69 -> D
Less than 60 - >F

You can use the following nested IF function:

IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))

In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

Here's what I did for the workshop activity.

   

Comments [2]

July 28, 2008

Functions: VLOOKUP + HLOOKUP

From Excel help (i.e. press F1). Italics and underlining are mine.

HLOOKUP
Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)


VLOOKUP
Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Comments [0]

July 28, 2008

Database Criteria: DGET

Learning how to use the Database functions like DGET, DCOUNT, DCOUNTA.

Here's one simple exercise we applied.

Suppose you have a table of names, and variables like Sales and Salary.
In this exercise, I want to find out the salary for Jon (imagine that the table is huge, and you don't want to scroll).

To create your customised criteria, first I replicate the fields in a separate cell (or sheet). See item outlined in the blue box, i.e. the row starting from E3 -- "Name", "Salary", "Sales". NOTE: the names have to be exact as in the original table (common mistake is to have an additional space character after the word, so check that).

This is the formula: =DGET(A:C,"salary",E11:E12), where:
  • A:C means I'm specifying the database, i.e. everything under columns A to C
  • "salary" is the text that I want to search (this part can be a number)
  • "E11:E12" refers to the table I created

I've made cell F6 equal to cell E4.
So when I key in "jon" in my table (kinda like a customised search table), Excel automatically retrieves the information. The limitation of DGET is that it can't handle duplicate names (I think it returns a #NUM! error).

Comments [0]

July 28, 2008

Data entry using a "Data Form"

We usually key in data into the Excel worksheet cells directly. I guess it's useful when you have a very long list (vertically or horizontally). Saves me from scrolling.

Make sure you select one of the cell in the data table.
Go to Data --> Form
(Click OK when you see the warning/ error message)

Comments [0]

July 28, 2008

Function: LARGE + SMALL array

This is really useful if I want to efficiently identify the largest/ smallest numerical values from an array (i.e. table).

=LARGE(<data table>, <position from the largest>)
=SMALL(<data table>, <position from the smallest>)

In this example, I wanted to identify the top and bottom three values from the table. In my formula, I fixed the array points to make it easy to copy the formula.

Comments [0]

July 28, 2008

Exercise: Nested Fuctions + Text Function + Concatenation + Formatting Text + Extracting Substring

We did this exercise where we had to apply what we learned about Functions.

I had a column of names, typed in lower-case. I was to create account numbers for them, in sequence. In this format: / serial-number/ Year

This is the formula that I built:
=UPPER(LEFT(A2,3))&"/"&TEXT(ROW(),"000")&"/"&ACC_YEAR

What it means:

1) To get the first three letters of the company name:
=UPPER(LEFT(A2,3))&"/"&TEXT(ROW(),"000")&"/"&ACC_YEAR

2) To convert to upper-case:
=UPPER(LEFT(A2,3))&"/"&TEXT(ROW(),"000")&"/"&ACC_YEAR

3) To automatically generate the numbers, I've based the number sequence on their respective rows, i.e. ROW( ).
=UPPER(LEFT(A2,3))&"/"&TEXT(ROW(),"000")&"/"&ACC_YEAR

4) Then I nest this function in the TEXT (, ) function, where the "000" means I want to display it as THREE digits, and automatically fills up any "empty" numerals with zero. E.g. if ROW () returns one digit, then it will insert "00" in front. If ROW () returns two digits, then it will only insert "0" to the two digits.
=UPPER(LEFT(A2,3))&"/"&TEXT(ROW(),"000")&"/"&ACC_YEAR

5) To add the year 2008, I chose to name a constant. I named it "ACC_YEAR".
=UPPER(LEFT(A2,3))&"/"&TEXT(ROW(),"000")&"/"&ACC_YEAR

6) Finally, to add in the "/", I use the & symbol and specify the "/" as the character
=UPPER(LEFT(A2,3))&"/"&TEXT(ROW(),"000")&"/"&ACC_YEAR

7) In the screen capture, notice that row 6 "ho & ho brothers" appears as two initials. 'Cos the function picked up the space as a character as well. If I want to remove that, I can build in another nested fuction using SUBSTITUTE.

Why SUBSTITUTE and not REPLACE? Well according to the Excel help:
"Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string."

   

Comments [0]

July 27, 2008

Function: Text

Several ways to manipulate Text in Excel.

=LOWER(<text cell>)
Converts a text string to all lower-case

=UPPER(<text cell>)
Converts a text string to all upper-case

=PROPER(<text cell>)
Converts a text string to Proper-case, i.e. first letter of each word would be in upper-case.
e.g. from "the quick brown fox" to "The Quick Brown Fox"

Comments [0]

July 27, 2008

Function: CONCATENATE

"Concatenate" is Excel's fancy way of saying "to join text items, or numbers, or single-cell references, into one item/ cell".
Basically you use the & symbol (i.e. amphersand) in the formula.

e.g. If I want to combine cells A1 and A2, my formula would be:
=A1&A2

If I want to insert a blank space between my combined text, I'll write it as:
=A1&" "&A2

Quite simple once you figure how it works.

Comments [0]

July 27, 2008

Nested Functions

Excel allows users to specify a Function within a another Function.

E.g. the CLEAN and TRIM function (for details of these functions, check the Excel help by pressing F1. Or select INSERT -> FUNCTION and search)

=TRIM(CLEAN(D7))

Comments [0]

July 27, 2008

Function: Transpose

Let's say I've got a column of numbers and I want to make them into a row.
Use the 'Transpose function'.

  1. Select the empty cells to move/ transpose the numbers (IMPORTANT: you must select an equal number of cells for the range you intend to specify. Or else this won't work)
  2. Type: =TRANSPOSE()
  3. Press CTRL-SHIFT-ENTER
     

Comments [0]