Walter Hildebrandt wrote:
On Wed, Nov 11, 2009 at 11:38 AM, Gene Young <[email protected]> wrote:
Walter Hildebrandt wrote:
On Wed, Nov 11, 2009 at 10:00 AM, Gene Young <[email protected]> wrote:
Walter Hildebrandt wrote:
I have the following formula:
IF*(**ISBLANK(A1)*;*"Empty"*;IF*(A1=0*;*"no
Div"*;A1-40+STYLE(IF*(A1>40*;
"RedText"))))
The syntax of an IF consists of three components:
1.
*The condition*
2.
*What to do if the condition is true*
3.
*What to do if the condition is false*
What is the syntax is the above formula? Where is the *false condition*
for
the *(**ISBLANK(A1)
IF*(A1=0*;*"no Div"*;A1-40+STYLE(IF*(A1>40*;"RedText")
Am I correct that you are saying that
A1-40+STYLE(IF*(A1>40*;"RedText"))))is the #3 False condition for both
(ISBLANK(A1) and (A1=0)
Am I correct that you are saying "RedText" is the #3 False condition for
(A1>40 even where it seems to be the #2 True condition since there is only
one condition after the (A1>40
You have to take careful notice of the brackets. Match them - every open
bracket needs a corresponding closing bracket. The first thing after an
IF is an opening bracket. The IF finishes with the closing bracket that
*corresponds* to that opening bracket. No action can relate to more than
one IF. Just read it carefully and, if necessary, translate it into English.
So your
IF(ISBLANK(A1);"Empty";IF(A1=0;"noDiv";A1-40+STYLE(IF(A1>40;"RedText"))))
there are *three* IFs. Translate into English:
If A1 is blank [#1 If] then
[#2 True] set the current cell to "Empty"
else [#3 False] if A1 is zero [#1 If]
[#2 True] set the current cell to "noDiv"
else [#3 False] set the current cell to the value A1 - 40 + <something>*
endif
* the value of <something> in this case is *always* zero because the STYLE function
always returns (programmer-speak for "yields") zero. However, as I said in an earlier
message on this subject, 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. We have
STYLE(IF(A1>40;"RedText")
Translate into English:
If A1 > 40 [#1 If] then
[#2 True] set the style to "RedText"
else [**implicit** #3 False] do nothing (see below) - don't change the current
cell's style.
endif
My apologies. I failed to notice that you left out a condition. You
need to change the last if statement to add the false condition:
The formula is working fine now. The numbers that are suppose to be black
are black and that numbers that are suppose to be red are red. Also the
text is the color it should be. A cell Style named RedText has been created
and the STYLE in the formula somehow activated the RedText style to give the
correct results in the various cells involved. Somehow the false condition
is included.
IF*(A1>40*;"RedText";"")
This states that if your condition is true, "RedText" if it is false, null.
You could of course put any other action in for false, but you must have
the else condition.
You can leave out the 3rd ("#3 False") action just as you can in
English. If you do leave it out there's an implied "do nothing".
One way I was taught to analyse brackets is to start at the IF with a
clenched fist; work through the formula from left to right; raise a
finger for every opening bracket and lower the most-recently-raised
finger for every closing bracket. You've go to the end of the IF when
you have no fingers raised. If you start at the right-most IF you can
easily break down complex formulae into their component parts.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
