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]
