You did an outstanding job in answering my questions. Your use of the numbers, the letters, and the // put the information in a way that is was understandable. Thank you
Walter On Fri, Nov 13, 2009 at 1:01 PM, Harold Fuchs < [email protected]> wrote: > 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] > >
