2009/11/9 Harold Fuchs <[email protected]>
2009/11/9 Walter Hildebrandt <[email protected]>
What does 0 mean and what is its effect in various condition?
In a numeric context, 0 means zero.
If you try to treat *text* in a numeric context then the text will
usually
be treated as zero.
For example : When I got the formula
=A1-40+IF(A1>40;STYLE("RedText");0),
I
was told “The STYLE function always returns 0, so the formula is
mathematically
equal to A1-40+0”
Correct. I don't know where you got that formula but it doesn't do
anything
useful. See below.
What is effect of the 0 at the end of the formula?
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
These are separated by semicolons. Thus your IF, which is
IF(A1>40;STYLE("RedText");0)
says
if the contents of cell A1 is greater than 40 [#1, the condition]
set the current cell's value to STYLE("RedText") [#2, what to do
if
the condition is true]
otherwise set the current cell's value to zero [#3, what to do if
the
condition is false]
However, all this is nonsense. Calc's Help on the Style function tells us
that its syntax is nothing like the one you cited and that it doesn't do
anything like what you've been told. I quote:
======== begin quote ====
Syntax
STYLE(style;time;style2)
Style is the name of a cell style assigned to the cell. Style names must
be
entered in quotation marks.
Time is an optional time range in seconds. If this parameter is missing
the
style will not be changed after a certain amount of time has passed.
Style2 is the optional name of a cell style assigned to the cell after a
certain amount of time has passed. If this parameter is missing
"Standard"
is assumed.
===end quote ===
For example if “Negative numbers red” is selected (is checked) when
formatting cells, 0:[RED]-0 appears in the Format code box.
For example I was told “Enter something like this in the format code
field:
0"%" This will just add the % character right after the number, but the
number will still act as an ordinary number.”
"Something like" is probably true depending on your definition of the
phrase. A dog is something like a horse, perhaps: 4 legs, tail, hair,
friend
of mankind. But ...
Also when entering the O, under different conditions, Is the 0 a zero
or
a
capital letter?
usually zero. A bare (unquoted) letter O would normally only be
meaningful
if you had *named* something (like a print-range) "O".
Why don't you use the Conditional Formatting menu to do what you want?
Sorry to reply to my own post but I've done some more research.
If you have your values in column A, then you can set column B to your
desired colours (and values) by putting in B1 the formula
=A1+STYLE(IF(CURRENT()>40; "Red"; IF(CURRENT()<40;"Green";"Default")))
and dragging it down the column.
This will set column B to have the same *values* as column A but with the
appropriate colours.
You are *adding* ("+") the value of the Style function, which is always
zero. But the function has the *side effect* of setting the style. Note
that
the If is "inside" the Style, not vice versa as you had it.
The If looks complicated but isn't. It's just the normal 3 parts separated
by semicolons. The interesting bit is that the third part of the first If
is
another If, which has the usual 3 parts. Just read it carefully.
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
