Open Office Functions

Scenario Feature in Open Office Calc

2008-11-28T01:20:00.001-06:00

Here is a video I created describing how to use the scenario feature in open office calc.

Split Data in a Cell

2008-11-25T02:38:00.005-06:00

Have you ever made a list of names in a spreadsheet and put the first and last name in one cell? Later on, you decide you want the first and last names to be in separate cells. Here is a video I created and posted on You Tube. Hope you enjoy.

For information on:
LEFT()
RIGHT()
LEN()
FIND()

you can see examples and explanations on my blog. Just look on the right hand side for the category the functions are in. They are grouped like they are in Calc.

FORECAST()

2008-11-24T00:17:00.003-06:00

FORECAST

Extrapolates future values based on existing x and y values.

Syntax

FORECAST(Value; DataY; DataX)

Value is the x value, for which the y value on the linear regression is to be returned.

DataY is the array or range of known y's.

DataX is the array or range of known x's.

Cells A2:A40 contain the x-values and cells B2:B40 contain the y-values.
Cell D2 contains Value.
Cell E2 contains the formula =FORECAST(D2;$B$2:$B$40;$A$2:$A$40)

Cells E3:E12 contains formulas similar to the formula in E2.

Below is a screen shot of my spreadsheet.

Note: Not all of the x- and y-values are shown in the screen shot.

Adding Just Odd Rows or Just Even Rows

2008-11-22T22:10:00.007-06:00

Here is a neat little trick to add just the odd rows or just the even rows in a range.

Functions used in the formula.

SUM()
IF()
MOD()
ROW()

You can find an explanation of each of the above functions on my blog.

Adding just the odd rows.

C1 contains the formula
{=SUM(IF(MOD(ROW(A1:A10);2)=1;A1:A10;0))}

Notice the braces around the formula. You must enter it as an array formula (Ctrl>Shift>Enter)

Adding just the even rows.

C1 contains the formula
{=SUM(IF(MOD(ROW(A1:A10);2)=0;A1:A10;0))}

Notice the braces around the formula. You must enter it as an array formula (Ctrl>Shift>Enter)

Notice the difference in the two formulas above. I have the differences highlighted in red.

We use a 1 in the formula to sum just the odd rows because whenever you divide an odd number by two you will get a remainder of one.

We use a 0 in the formula to sum just the even rows because whenever you divide an even number by two you will get a remainder of zero.

MOD()

2008-11-22T21:50:00.003-06:00

MOD() - This function returns the remainder when one integer is divided by another.

Syntax:

MOD(Dividend;Divisor)

Example:

=MOD(25;4) returns 1
=MOD(38;9) returns 2

ROW()

2008-11-22T15:52:00.004-06:00

ROW() - If a single cell is selected, this function returns the row number of a cell reference. If a range is selected, it returns the corresponding row numbers in a one-column array.

Note: If a cell range is selected, the formula must be entered as an array formula (Ctrl>Shift>Enter). If a range is selected and the ROW function is not entered as an array formula, only the the first range cell will be returned.

Example 1: The first example will return the row number a single cell reference.

Cell A1 contains the formula =ROW(A7) which returns a value of 7.

Screen shot

Example 2: The second example will return the row numbers of a one-column array.

Cell A1 contains the formula {=ROW(B1:D5)} See screen shot below for results.

Notice the formula in the second example has braces around it. This is because the the function was entered as an array formula.

Screen shot

Format Paintbrush

2008-11-21T08:31:00.009-06:00

The format paintbrush is a nice little tool that allows you to apply the attributes of currently selected cell to another cell or cells.

Select the cell whose attributes you want to apply to another cell or cells.
Click the Format Paintbrush icon on the toolbar.
There will be a paint bucket next to the cursor. Select the cell or cells you would like to apply the changes too.

Here are some screen shots of the process.

Select the cell whose attributes you want to copy.

Select the Format Paintbrush icon.

There will be a paint bucket next to the cursor. Select the cell or cells you want to apply the changes too. I did C1 to F1.

Windows 7

2008-11-03T23:50:00.003-06:00

Well microsoft is coming out with its new operating system. It is called Windows 7. Supposedly, they are shooting for simplicity. I was one of the unfortunate ones to get stuck with Vista. Microsoft knows it sucks. So what now. I have to dish out a couple of hundred bucks to get Windows 7. Why doesn't microsoft allow Vista users to have a free upgrade to Windows 7? I have never used an operating system as annoying as Vista. For the last time, I want to allow this. That gets so annoying. Microsoft's problem is they are trying to control what the user is doing. Let the people decide for themselves. So, come on microsoft, gives us unfortunate Vista users a break and let us have Windows 7 for free. After all, you are the ones that dumped this garbage on us.

HLOOKUP()

2008-11-03T19:49:00.006-06:00

HLOOKUP() - This function searches across the top row in an array and returns a value in the same column in a specified row.

Syntax:

HLOOKUP(SearchCriteria;Array;Index;Sorted)

SearchCriteria - What you are wanting to match.
Array - The array you want to search through.
Index - Row number in the array from witch the matching value will be returned.
Sorted - If TRUE, HLOOKUP will look for an approximate match. If FALSE, HLOOKUP will look for an exact match.

Example:

I wanted to find the number of Highlander Hybrid's sold during the month of August. Below is the formula I have in cell "B22".

=HLOOKUP(I1;A1:M19;10;0)

"I1" represents the month I am looking for. I could have typed "August" instead of the cell address.

A1:M19 is the range I am looking through.

10 the row the Highlander Hybrid is in.

O which is the same as false means I want an exact match.

Frequency

2008-10-29T23:03:00.003-05:00

Frequency - This function counts the number of values in the Data array that are within the values given by the Classes array.

Syntax:

FREQUENCY(Data; Classes)
Data - represents the reference to the values to be counted.
Classes - represents the array of the limit values.

Screen shot. You can click the picture to enlarge.

Cells B14:B20 contain the formula {=FREQUENCY(A1:J10;A14:A20)}

Notice the {} around the formula. This indicates an array formula. To enter an array formula you must press the key combination Ctrl+Shift+Enter. However, I would recommend using the function wizard.
Cells A1:J10 contain the data values. I randomly generated numbers ranging form 50 to 120. Cells A14:A20 contain the classes. I broke the classes into intervals of length 10. You can click on the image to see a more detailed description of the classes.

Lotto Number Generators

2008-08-09T11:06:00.001-05:00

I have updated:

Hot Lotto Number Generator
Powerball Number Generator
Mega-Millions Number Generator

Lotto Number Generators

2008-08-06T10:30:00.001-05:00

I have updated:

Powerball Number Generator
Hot Lotto Number Generator
Mega-Millions Number Generator

Lotto Number Generators

2008-08-02T15:16:00.000-05:00

I have updated the Lotto Number Generators

Powerball Number Generator
Hot Lotto Number Generator
Mega-Millions Number Generator

Need Sticky Notes

2008-07-30T19:27:00.002-05:00

Need some sticky notes for your computer. ePad995 is a great for this. I downloaded and installed it about 3 years ago and love it. You can put sticky notes on your desktop to help remind you of things.

Click here to read about it and download it.

Lotto Number Generators

2008-07-29T22:13:00.000-05:00

I have updated:

Powerball Number Generator
Hot Lotto Number Generator
Mega-Million Number Generator

Lotto Number Generators

2008-07-25T15:27:00.000-05:00

I have updated:

Powerball Number Generator
Hot Lotto Number Generator
Mega-Millions Number Generator

Lotto Number Generators

2008-07-22T21:34:00.000-05:00

I have updated:

Powerball Number Generator
Hot Lotto Number Generator
Meg-Millions Number Generator

Find You Next Dig with Google Earth

2008-07-21T15:12:00.005-05:00

Scholar finds new archaeological sites by Googling.

Indiana Jones's next adventure may well be in front of a computer if Hollywood scriptwriters decide to embrace a new approach to archaeological research in war-torn zones.
David Thomas, a Ph.D. student in La Trobe University's archaeological program in Melbourne, has used Google Earth to safely uncover historic sites in a remote part of war-torn Afghanistan.

Using the free Internet resource, Thomas found up to 450 possible archaeological sites in Registan, which borders Helmand and Kandahar provinces in southern Afghanistan. The decision to use Google Earth was "partly born out of adversity", Thomas said, when a planned field trip was cancelled because of security concerns.

That is great he can use this technology to find sites like this. My only question. How are they going to do anything. I mean the reason they didn't go in the first place was because it is too dangerous.

SUBSTITUTE()

2008-07-21T14:53:00.002-05:00

SUBSTITUTE() - This function substitutes new text for old text in a string.

Syntax

SUBSTITUTE(text; search_text; new text; occurrence)

text is the text in which text segments are to be exchanged.

search_text is the text segment that is to be replaced (a number of times).

new text is the text that is to replace the text segment.

occurrence (optional) indicates which occurrence of the search text is to be replaced. If this parameter is missing the search text is replaced throughout.

Example:

=SUBSTITUTE("321032103210";"0";"Hello") returns 321Hello321Hello321Hello

=SUBSTITUTE("321032103210";"0";"Hello";2) returns 3210321Hello3210

FIND()

2008-07-21T14:39:00.003-05:00

FIND() -This functions looks for a string of text within another string. You can define where to begin the search. The search term can be a number or any string of characters. The search is case-sensitive.

Syntax

FIND(find_text; text; position)

find_text refers to the text to be found.

text is the text where the search takes place.

position (optional) is the position in the text from which the search starts.

Example:

=FIND("f";"abcdefghijk") returns 6

=FIND("f";"a b c d e f g h i j k") returns 11

=FIND(81;3958362387817) returns 14

LEN()

2008-07-21T14:30:00.002-05:00

LEN() - This function returns the length of a string including spaces.

Syntax

LEN(text)

text is the text whose length is to be determined.

Example:

=LEN("JimBob") returns 6

=LEN(Jim Bob") return 7

RIGHT()

2008-07-21T14:26:00.002-05:00

RIGHT() - This function returns the last character or characters of a text.

Syntax

RIGHT(text; number)

text is the text of which the right part is to be determined.

number (optional) is the number of characters from the right part of the text.

Example:

=RIGHT("Southbound",5) returns bound

=RIGHT("Southbound") return d

LEFT()

2008-07-21T14:14:00.005-05:00

LEFT() - This function returns the first character or characters of a text.

Syntax

LEFT(text; number)

text is the text where the initial partial words are to be determined.

Number (optional) specifies the number of characters for the start text. If this parameter is not defined, one character is returned.

Example:

=LEFT("Southbound";5) returns South

=LEFT("Southbound") returns S

CONCATENATE() or &

2008-07-21T13:53:00.004-05:00

CONCATENATE() - This function combines several text strings into one string.

Syntax

CONCATENATE(Text 1;...;Text 30)

Text 1; text 2; ... represent up to 30 text passages which are to be combined into one string.

Example: Look at the screen shot below.

Column A contains the last name of a person and column B contains the first name of person. I want to combine these into one cell.

Cell C2 contains the formula =CONCATENATE(B2;A2) which returns TreyCook
Notice the above result does not put a space between the first and last names.

Cell D2 contains the formula =CONCATENATE(B2;" ";A2) which returns Trey Cook
This result put a space between the first and last name. Notice the difference in the formulas. The second one has " ". Note the double quotes has a space between them.

Another approach to combine several text strings into on string is to use "&" without the quotes.

Cell E2 contains the formula =B2&A2 which returns TreyCook
You can see this gives the same result as in cell C2.

Cell F2 contains the formula =B2&" "&A2 which returns Trey Cook
This gives the same result as in cell D2.

Screen Shot

CHAR()

2008-07-21T13:40:00.003-05:00

CHAR() - This functions converts a number into a character according to the current code table.

Syntax

CHAR(number)

number is a number between 1 and 255 representing the code value for the character.

Example:

=CHAR(65) would return A

Here is a screen shot of all of the codes. You can download this workbook. Click on "Spreadsheet Function Files" under "Links" on the right hand side of the page.