2009/11/11 Walter Hildebrandt <[email protected]>
> I am trying to learn the syntax of the IF formula.so I can modify the
> formula into a new, modified formula.
>
> There are three opened brackets (ISBLANK(A1), (A1=0, and (IF(A1>40). All
> the
> IF( must be closed with a corresponding closing bracket. the closing
> brackets at the end of the formula seems to be doing the following;
>
> IF(ISBLANK(A1);"Empty";A1-40+STYLE(IF(A1>40;"RedText")
>
The above is syntactically incorrect and would generate an error message in
Calc. The correct version is
=IF ( ISBLANK(A1) ; "Empty" ;IF (A1=0 ; "no Div" ;A1-40+STYLE(IF (A1>40
;"RedText"))))
and it is this version that I will discuss.
The first If has three parts separated by two semicolons:
#1 ISBLANK(A1) which is the "test" of the If ("if A1 is blank")
#2 "Empty" which is the "True" action.
#3 IF (A1=0 ; "no Div" ;A1-40+STYLE(IF (A1>40 ;"RedText"))) which is the
"False" action. Yes, really. That whole thing is a single action to be taken
if A1 is not blank.
The second If also has three parts separated by two semicolons:
#1 A1=0 which is the test ("if A1 is zero").
#2 "no Div" which is the "True" action.
#3 A1-40+STYLE(IF (A1>40 ;"RedText")) which is the "False" action. Again,
that whole thing is a single action.
The third If only has two parts separated by a single semicolon:
#1 A1>40 which is the test ("if A1 is greater than 40")
#2 "RedText" which is the "True" action.
The third part (the "False" action) is missing. An If can have its "False"
action omitted in which case the result is zero/blank/null depending on
context. In this case either will work because STYLE(""), STYLE(0) and
STYLE(" ") all have the same effect - "do nothing".
> The False is A1-40+STYLE(IF(A1>40;"RedText")) and I do not know what that
> means.
I have told you what it means. It instructs Calc to set the current cell to
the value obtained by by subtracting 40 from the value of cell A1 [A1-40]
and then adding the result of the STYLE function [+STYLE(...)]. The result
of the STYLE finction is *always* zero but, as a *side effect* the function
can change the style of the current cell.
In this case the STYLE function is told to change the style of the current
cell based on the result of an IF. The IF says that if A1 is greater than
40, the result should be "RedText", otherwise it should be nothing at all -
because the 3rd part of the IF is (legally) missing. So the result is either
STYLE("RedText") or STYLE("") depending on the result of the If. So the
STYLE function either changes the style of the current cell to "RedText"
(presumably a named style in your spreadsheet) or to nothing at all - which
results in no change.
> The same thing is true for the IF(A1=0; The
> A1-40+STYLE(IF(A1>40;"RedText")) is the False
>
> Am I correct that the above is saying A1-40+STYLE(IF(A1>40;"RedText"
> (RedText) is the False answer for these two Ifs?
>
**No**. Nothing can belong to two separate IFs. Ever. Ever. Never.
>
> There is no; before the (IF(A1>40). How do account for the third opened
> bracket?
>
There is no semicolon there because that IF is part of the STYLE function
STYLE(IF(A1>40;"RedText"))
The semicolon comes before the A1-40+STYLE(IF(A1>40;"RedText")) *all* of
which is the False action of the 2nd If.
> The STYLE function can change the style of the current cell as a “side
> effect”. In this case the STYLE function is being told to change the style
> of the current cell depending on the value of A1
>
> Does this means the cell style RedText will control the formatting of the
> current cells?
The style RedText will be applied to the current cell if A1 is greater than
40.
> Does this mean if another style is created, that new cell
> style can be entered in the formula in the place of RedText?
>
Yes. The STYLE function is quite complicated. You can read about it in the
Help. Here we are using it to mean either STYLE("RedText") or, implicitly,
STYLE("") depending on the result of the If. That's because the If either
yields "RedText" or "" depending on the value of A1.
The style RedText will be applied to the current cell if A1 is greater than
40.
> The only thing I did in setting up the RedText cell style was putting
> RedText as the name in the Organizer and selected Light Red in place of
> Automatic in the Font Effects.
>
That's all that's needed. But the style could be as complicated as you like
- different font, italic, etc. etc. Anything you're allowed to define when
you define a style.
> Walter
>
<snip>
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
