Walter Hildebrandt wrote:
I am still confused of the semicolons. The following is a color coded
summary of what you have said.
The colour coding never made it through to my computers.
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
This is wrong. The STYLE function is *only* invoked if A1 is neither
blank nor zero. If A1 is neither blank nor zero then the STYLE function
sets the current cell's style to "RedText" if A1 is greater than 40 but
to "" otherwise.
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]
Your formula is
=IF ( ISBLANK(A1) ; "Empty" ;IF (A1=0 ; "noDiv" ;A1-40+STYLE(IF (A1>40
;"RedText"))))
-A-----------------1---------2B--------3---------4------------C---------5
Here I have labelled the IFs A, B and C and I have numbered the
semicolons 1, 2, ... 5
Semicolons 1 and 2 separate the three parts of IF A.
Semicolons 3 and 4 separate the three parts of IF B
Semicolon 5 separates the *two* parts of IF C. An IF can have only two
parts, in which case the first is the "Test" or "Condition" and the
second is the "True" action. In these cases the "False" action is
implied to be null/blank/zero depending on context.
The STYLE function can have 1, 2 or 3 parts ("arguments" or
"parameters"). I won't go into the gory details but in your case the
STYLE function is being used with only *one* argument. That argument is
the whole of the IF: IF (A1>40 ;"RedText"). You can see this because the
STYLE function is
STYLE(IF (A1>40 ;"RedText"))
-----A---B----------------YZ
Here I have lettered the brackets in such a way that opening bracket A
is closed by Z and B is closed by Y. Brackets A and Z contain the
*single* argument of the STYLE function. Brackets B and Y contain the
*two* parts of the IF. So this thing is saying "if A1 > 40 then set
Style to "RedText" else set Style to (implied) "". Setting the Style to
"" means "do nothing to the existing Style of the cell in question".
Splitting the IFs on semicolons, indenting and adding some English
(separated from "code" by "//") gives
=IF ( ISBLANK(A1) ; // If cell A1 is blank
// then
"Empty" ; // set the current cell to "Empty"
// else
IF (A1=0 ; // if cell A1 is zero
// then
"noDiv" ; //set the current cell to "noDiv"
//else
A1-40+STYLE(IF (A1>40 ;"RedText")))) //set
the current cell to A1-40 *and* set the current cell's style to X where
X is given by
IF (A1>40 ; // if A1 is greater than 40
// then
"RedText") //X becomes
"RedText"
// else
// implicitly X becomes ""
//endif
// endif
// endif
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
