VLOOKUPis useful as long as we are looking on the right side of the table. There is however a way to circumvent this restriction - as is illustrated in the example below.
We actually have set aside VLOOKUP and use a combination of OFFSET and MATCH instead.
The MATCH function lets us know what row we are interested in, and then the OFFSET puts us in the correct column.
For a 2d table lookup, we have to put VLOOKUP aside and use the OFFSET function in conjunction with MATCH as illustrated in the example below.
Basically, each MATCH function is responsible for determining the correct row/column respectively of the table.
In the lookup table, we list major network affiliates for a set of major cities.
On another sheet, we see how the correct affiliate can be determined - given the correct pair of keys…
Another useful application of the VLOOKUP function is when
using OpenOffice Calc to create invoices.
On one sheet - we have the catalog - where we can maintain the prices and the stock items in one location.
The VLOOKUP function comes into play on the invoice sheet - as is illustrated below. Here, the lookup table (the catalog) is on another sheet)
Here is a simple grading example to illustrate how VLOOKUP can make life easy for you.
In the formula, the final argument 1 (TRUE) tells OpenOffice Calc that the lookup column is ordered and an exact match is not necessary. The closest match that does not exceed the lookup value is returned - which is exactly what we want.
Use this procedure to automatically increment data as you fill down a column or across a row.
1.Select the cell range whose existing contents you want to extend to more cells. Then release the mouse button.
2.Move the mouse pointer on top of the automatic fill handle in the lower right of the selected cell. The mouse cursor turns into a target cross.
3.Click and hold down the left mouse button on the automatic fill handle, and drag in the desired direction of the fill, selecting all the cells you want to contain data.
Here are a few examples of how the automatic fill sequence works:
The sequence 1, 2 becomes 1, 2, 3, 4, and so on
The sequence 1, 3 becomes 1, 3, 5, 7, and so on
The cell content “Quarter 1″ becomes Quarter 1, Quarter 2, Quarter 3, and so on
Automatic fill only increments data that OpenOffice.org Calc recognizes, such as numbers, dates and cell references. If Calc does not recognize data, automatic fill duplicates the data exactly. However, you can create sort lists that contain lists that Calc will recognize and automatically increment. To create a sort list, choose Tools - Options - Spreadsheet - Sort Lists
If you just want to copy the range without changing the values, hold down the Ctrl key while dragging.
p>In mathematics, the greatest common divisor (abbreviated GCD), or highest common factor (HCF) of two integers which are not both zero is the largest integer that divides both numbers.
The GCD of a and b is often written as gcd(a,b) or simply (a,b). For example, gcd(12,18) = 6, gcd(-4,14) = 2 and gcd(5,0) = 5. Two numbers are called coprime or relatively prime if their greatest common divisor equals 1. For example, 9 and 28 are relatively prime.
The greatest common divisor is useful for writing fractions in lowest terms.
The algorithm for determining the GCD of two numbers is known as a recursive algorithm - and the OpenOffice Basic code is listed below..
Function gcd(a As Integer, b As Integer) As Integer
If b = 0 Then
gcd = a
Else
gcd = gcd(b,a)
End If
End Function
One of the most useful and versatile builtin functions in OOo Calc, with a bit of creativity, you can use for a slew of tasks…
How many cells in a given range are negative.
=COUNTIF(A1:A20;”<0")
How many cells have the string “Yes”.
=COUNTIF(A1:A20;”Yes”)
Don’t forget to include the quotation marks around the condition argurment. Otherwise, you get an Err:510
Stefano Lenzi commented if you want that your criteria contains reference to other cells you can refer to the following example.
To count the cells less then A3:
=COUNTIF(”A1:A20″;CONCATENATE(”<";A3))
Here is another few simple macros - written in OpenOffice Basic. First we have a primality test …
Function IsPrime(Val As Integer) As Boolean
Dim I As Integer
IsPrime = FALSE
For I = 2 To Val - 1
If Val MOD I = 0 Then
Exit Function
End If
Next I
IsPrime = TRUE
End Function
The second macro finds the next highest prime.
Function NextHighestPrime(Val As Integer) As Integer
Dim I As Integer, PrimeFound As Boolean
I = Val
NextHighestPrime = Val
PrimeFound = FALSE
Do While PrimeFound = FALSE
I = I + 1
If IsPrime(I) = TRUE Then
PrimeFound = TRUE
NextHighestPrime = I
End If
Loop
End Function
When learning a new (computer) language - it is important to have a good mastery of the basics. Developing simple functions like this is what is called for here.
Function Fibonacci(Val As Integer) As Long
Dim I As Integer
Fibonacci = 1
For I = 1 To Val
Fibonacci = Fibonacci * I
Next I
End Function
We can now call our newly created function from the spreadsheet - just like the builtin OOo Calc functions.
The builtin OOo Calc function SQRT returns the square root of it’s numerical argument. There are no
builtin functions for cube roots or higher - but you can use the exponentiation operator to derive any
order of root - as is illustrated below.
As you may notice, the parentheses are required in the example - because of the higher order of
precedence of the “^” operator over “/”. If you are not sure of the precedence rules - it is better
to add the parentheses anyway - as it makes for more legible equations.
You are currently browsing the OpenOffice Calc Tips weblog archives for September, 2004.