I am still confused of the semicolons. The following is a color coded
summary of what you have said.
1st IF
=IF(ISBLANK(A1) ;*"Empty"*;*IF(A1=0;"no Div";A1-40+STYLE(IF(A1>40;"RedText"*
))))
IF(ISBLANK(A1) (test)
*"Empty" (True)*
*IF(A1=0 ;"no Div";A1-40+STYLE(IF(A1>40 ;"RedText"))) (False)*
The function +STYLE changes the style of the current cell so that the *True
*and *False* for this IF is in effect. The RedText style only take effect if
*False* is the result in the current cell
2nd IF
=IF(ISBLANK(A1) ;"Empty";IF(A1=0;*"no Div"*;*A1-40+STYLE(IF(A1>40;"RedText"*
))))
IF(A1=0 (test).
*"no Div" (True)*
*A1-40+STYLE(IF (A1>40 ;"RedText")) (False)*
The function +STYLE changes the style of the current cell so that the *True
* and *False* for this IF is in effect The RedText style only take effect if
*False* is the result in the current cell
3rd IF
=IF(ISBLANK(A1) ;"Empty";IF(A1=0;"no Div";A1-40+STYLE(IF(A1>40;*"RedText"*
))))
IF(A1>40 (test)
*"RedText" (True)*
*(formula is blank) (nothing is done) (False)*
False is omitted therefore 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 function +STYLE changes the style of the current cell so that the *True*and
*False* for this IF is in effect. The RedText style take effect if *True* is
the result in the current cell
For the 1st IF, what is the "rule" or "principal" that caused the semicolon
at the end of the following to be in effect?
=IF(ISBLANK(A1) ;*"Empty"*;*IF(A1=0;"no Div";*
What is the “rule” or “principal for the 2nd IF. The semicolon at the end of
the following seems to work correctly
=IF(ISBLANK(A1) ;"Empty";IF(A1=0;*"no Div"*;
however the semicolon after the 40 and before “RedText” did not seem to take
effect
Walter
On Thu, Nov 12, 2009 at 4:20 AM, Harold Fuchs <
[email protected]> wrote:
> 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]
>
