This episode of HBR is brought to you by AnanasToast.com.
Get 15% discount on all shared hosting with the offer code HBR15.
That's HBR15.
Bit your web hosting that's Aniston Fair at AnanasToast.com.
Hello, this is Ahuka, welcoming you to another exciting episode of Hacker Public
Radio in our ongoing series on Libra, Office, Calc.
We have looked over the last number of weeks at discussions of the functions first of all the
financial functions and then the statistical functions in some depth.
I don't propose to go into the remaining types of function in that kind of depth.
Those were the two that I thought had the most payoff for the largest number of people.
Doing that with all the functions we'd be here for another year and I don't think that's a good
use of my time but I want to do kind of a little survey of just some of the other things.
So you know what's there.
And if you followed the stuff that I've already done, you've already got a pretty good idea
of how to use functions so that I don't think we really need to go too much more into all
of these details.
Remember that if you need to know more about the Google is your friend and I would just
mention that most of these functions also exist in Excel.
So if you find a site that explains how it works in Microsoft Excel, it probably works pretty
much the same in Libra, Office, Calc.
Now, first thing I want to look at are the database functions.
These functions are all very similar.
They take three arguments database field and search criteria.
Each one has a definition and in the show notes, I'm going to have a link to the Libra
Office help site that is going to explain more about all of these database functions.
So database itself is the cell range defining the database.
Database field specifies the column where the function operates after the search criteria,
the first parameter is applied and the data rows are selected.
To reference a column by means of the column header name, place quotation marks around
the header name.
Search criteria is the cell range containing search criteria.
If you write several criteria in one row, they are connected by an end.
If you write the criteria in different rows, they are connected by OR.
Type D cells in the search criteria range will be ignored.
Many of these functions support regular expressions as search criteria.
So in brief, these functions let you do a query through the search criteria and pull
the rows that correspond to that query, then do something with them, but something you can
do.
Well, you can do an average count, max, min, standard deviation, sum variance, those are
some of the things you might want to do there.
Now, the next group, I want to look at information functions.
These functions let you ask questions about the contents of cells in your spreadsheet.
In general, you can imagine a long column of numbers and wanting to know something about
each of the numbers.
So in an adjacent column, you put one of the information functions, pointed at the first
column and dragged to fill the column.
If the number in the first column matches what you're looking for, the function will
turn a value of true.
Here are some of the things you can test for.
Is blank?
ISBNK is the cell blank.
The zero IS-E-R-R-O-R does the cell contain an error value.
Is, even, underscore, add is the number even.
Is formula does the cell contain a formula.
IS-N-A returns a true value if the value in the cell is equal to hash and slash A.
Is non-text returns a true value if the value in the cell is not text.
Is number returns true if the value is a number.
Is odd underscore ADD is the number odd?
Is-RF returns true if the value is a reference and is text returns true if the value
is text.
So that's some of the information functions.
Then there's the logical functions.
These are functions where you can apply logical tests of some kind and get back and
answer.
For example, maybe you want to know about any rows where the value in column A is greater
than 10 and the value in column B is less than 15.
These are logical tests.
The form of these functions is to specify as many of these logical tests as you like.
I'm up to 30 actually and return a true if all tests are passed.
So imagine you have two columns of numbers, A and B, and you want to apply the example
of the test that I gave in column C enter the function and in cell C1 and in logical
value, one enter the test A1 greater than 10 and in logical value 2 enter the test B1
less than 15.
Click OK then drag to fill column C.
This will test all the rows that you have filled and here are the logical functions of
this kind and returns true if all arguments are true or returns true if any argument
is true and x or exclusive or returns true if an odd number of arguments is true.
If that last one seems a little weird to you and x or can seem a little bit weird, there's
a side light that's one of those things that you run into constantly when you're studying
cryptography.
I would say consult a book on logic and see what exactly is meant by exclusive or I'm just
telling you it's here.
So also within logical functions, you have some interesting functions here for if-then calculations.
These functions all in general apply a test then allow you to specify two values.
One of the test is true, the other if the test is false.
So these are if, for this function you need to give it the test as the first argument.
For example, if the value in column A is positive and to one thing if it is negative and
or something else, if error, here the test is built into the function, it is to see if the
cell in the column has an error.
If it does return one value, if it doesn't return a different value.
If an A, this test is also one that is built into the function.
If it checks for a hash n slash a error in the cell and then depending on what is
there it returns one of two values.
Mathematical functions, there's a ton of them in Calc and possibly our friend Charles
and New Jersey will want to talk about some of those some day.
What I want to do right now is simply say there's a lot of them.
Again with, you've got all of the trigonometric functions, sign, cosine, tangent, secant,
cotangent, cos, secant, arc sign, arc cosine, arc tangent, arc cotangent, hyperbolic
sign, hyperbolic cosine, hyperbolic tangent, hyperbolic secant, hyperbolic cotangent, hyperbolic
cos, you can't, you know there's an awful lot of trigonometric functions, but I suppose
they need to be there for some reason.
Then you can convert degrees to radians and vice versa.
There's the random functions that we've already talked about and then there are the exponentials
rounding sums, including the sum of the series, absolute value and many more.
Then there are the array functions.
These include some of the standard linear algebra manipulations such as finding the determinant,
inverting the array, multiplying two arrays, inner products, various summing of squares,
functions and transposing rows and columns of an array.
There are also functions to get regression results calculated as an array.
Spreadsheet functions, this is a bit of a grab bag.
You have the lookup functions, applying a URL hyperlink, extracting data from a pivot table,
linking via dynamic data exchange to an external data source and so on.
You can also apply a style to a cell or a range of cells.
We're going to be talking about pivot tables and styles in more depth later on in this series.
Text functions.
It's another pretty miscellaneous group.
I will mention some of the ones that you might want to use, but you can explore this further
sometime if you want to do a deep dive.
But what are some of the text functions?
Erobic numerals to Roman numerals and vice versa.
So there's one function called Arabic and another one called Roman.
Convert a unicode number into the corresponding character or find the number for a given
character.
Those are UNA, UNICHAR and UNICHOD.
Convert text to uppercase or lowercase.
The functions are upper and lower.
Concontinate several text strings that's a function concatenate.
Compare two cells and see if they have the exact same text strings that function is called
exact.
Find a text string using the find.
Then find the first or last character of a text string using left or right.
Remember you can always drag to fill a column and apply these to many cells at once.
For example, I've had a occasion to compare two long columns of numbers to see if any
and one column were missing the other.
The exact function proved very useful for that.
Add in functions.
There's a lot of functions here that would be used to a programmer.
You've got the Bessel functions, Bessel i, Bessel j, Bessel k, Bessel y.
Conversion between binary, decimal, hexadecimal and octal numbers.
These usually have something like bin to deck, bin to hex, bin to oct deck to bin and that
sort of thing where the two is the numeral two and not the word not the letters to you.
So you've got a bunch of those.
You've got various functions for dealing with imaginary and complex numbers and functions
like complex imaginary, I am argument, I am C.O.S. and so on.
So this concludes our look at functions in Excel Cal.
I hope you agree there is a lot of power in these functions and that to be a proficient
spreadsheet user.
You really need to have at least a few of them in your toolkit, which ones you will
use will of course depend on what your interests are.
There are functions for accountants, stockbrokers, statisticians, scientists, engineers,
programmers and so on.
So probably no one uses every function.
You need to learn the ones that are of interest to you or are useful in the work that you
are doing.
So this is Ahoka, signing off for Hacker Public Radio and is always reminding you to
support free software.
Bye-bye.
You've been listening to Hacker Public Radio as Hacker Public Radio.org.
We are a community podcast network that releases shows every weekday Monday through Friday.
Today's show, like all our shows, was contributed by a HPR and a snare like yourself.
If you ever thought of recording a podcast, then click on our contributing to find out
how easy it really is.
Hacker Public Radio was founded by the digital.com and the informomicon computer club and
is part of the binary revolution at bnref.com.
If you have comments on today's show, please email the host directly, leave a comment
on the website or record a follow up episode yourself on this otherwise status.
Today's show is released on the creative comments, attribution, share a like, 3.0 license.