53*12
A$4+$A$12
B5*12
SUM(A1:A1000)
AVERAGE(C50:C100)
Vlookup(a1;b1:d100;3;0)
As you might see, this opens up for a lot of exciting possibilities! This is one of the real strengths of a spreadsheet; it is relatively easy to ad some level of intelligence. Do you see something peculiar about the formulas above? None of them start with an "=". This is intentional, as you do not need to start a formula with "=" as long as it is used inside another formula. Now look at the third an final parameter: [If the statement is FALSE, perform this] If the first parameter is FALSE, this parameter will tell Calc what to do next. You can put a value or formula here, such as:
53*12
A$4+$A$12
B5*12
SUM(A1:A1000)
AVERAGE(C50:C100)
Vlookup(a1;b1:d100;3;0)
As
you see, this is very much the same as with the previous
parameter.
But let us ad something here; it is very common
to ad yet another IF()-statement in the final parameter. It can also
be used in the second parameter, but most feel it gives a better
structure to ad it to the final parameter. We will go further into
this in a later lesson.
Finally, we are done with the
theory, now we can start using it...
In A1,
type "Name:",
in B1 type
"Kari",
in A2,
type "Age:"
and in B2 type
"35",
as shown above.
In
cell A4,
enter the formula as shown above.
Then hit [Enter].
If everything is typed correctly, it should show
35.
First Calc checks
wether [B1="Kari"] is
correct. If it is, it will show the contents of [B2].
Otherwise, it will show the word Unknown, hence ["Unknown"].
When
working with text, it is very important to use the " at
the beginning and end of the text string inside formulas.
Change
the name in B1 to
"Steven",
hit [Return]
and observe what happens.
The
value in A4 changed
to "Unknown",
which is what the third parameter states.
Now I really
encourage you to experiment with this formula, as this will help you
leverage some of the phenomenal powers of Calc!
Try putting a cell-reference or another formula in the first
parameter, try entering a number-value in the second parameter etc.
=LEN()
How to count characters in a cell or string
All
this formula does, is to count the number of characters in either a
text string or a cell, using a cell reference.This is a formula that
is mostly used in conjunction with other formulas, as all it
provides, a the number of characters.
The syntax for the
formula is:
=LEN([Either a
"text string" or a cell reference])
Here
is an example:
Go to cell A1 and
type =LEN("This is a
test!") and hit
[Enter].
What
happens? You should get the number 15 as a result, as there are 15
characters in the text string.
Here
is another example:
Delete the
contents of cell A1.
Enter a text string in A1,
for instance "This is the second
test!"
Go
to cell A2 and
type:
=LEN(A1)
and
hit [Enter].
Cell A2 should
now give you the number 24 (if you used the suggested text
above).
Go
back to A1 and
type something else, and see how the number in A2 changes
accordingly.
Please note that spaces are regarded as
characters!