color of numbers in Calc

[Walter Hildebrandt-6]

Cell A1 has a number.
Cell B1 is to have how much less, or much more, A1 is than 40.
When A1 is less than 40 the answer in B1 is to be in black.
When A1 is more than 40 the answer in B1 is to be in red.
.
This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
is black.

[Richard Detwiler]

Walter Hildebrandt wrote:
> Cell A1 has a number.
> Cell B1 is to have how much less, or much more, A1 is than 40.
> When A1 is less than 40 the answer in B1 is to be in black.
> When A1 is more than 40 the answer in B1 is to be in red.
> .
> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
> is black.
>
>  

What you want to do is exactly the purpose of Conditional Formatting.
(menu path Format > Conditional Formatting)

Look up Conditional Formatting in Help for specific guidance on how to
use it. If stuck after doing that, I can walk you through it, but it's
not particularly difficult.



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Walter Hildebrandt-6]

Please seed some guidance on how to get starter.  I have tried various
combinations in the Conditional Formatting wizard but none of them worked.

On Fri, Nov 6, 2009 at 2:44 PM, Richard Detwiler <RLShadow@...> wrote:

> Walter Hildebrandt wrote:
>
>> Cell A1 has a number.
>> Cell B1 is to have how much less, or much more, A1 is than 40.
>> When A1 is less than 40 the answer in B1 is to be in black.
>> When A1 is more than 40 the answer in B1 is to be in red.
>> .
>> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
>> is black.
>>
>>
>>
>
> What you want to do is exactly the purpose of Conditional Formatting. (menu
> path Format > Conditional Formatting)
>
> Look up Conditional Formatting in Help for specific guidance on how to use
> it. If stuck after doing that, I can walk you through it, but it's not
> particularly difficult.
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

[Richard Detwiler]

Walter Hildebrandt wrote:
> Please seed some guidance on how to get starter.  I have tried various
> combinations in the Conditional Formatting wizard but none of them worked.
>  

Walter: I'll be glad to do that, but just be advised it might be late
Saturday before I'll have time to write out the steps, as I'm tied up
most of the day tomorrow.

> On Fri, Nov 6, 2009 at 2:44 PM, Richard Detwiler <RLShadow@...> wrote:
>
>  
>> Walter Hildebrandt wrote:
>>
>>    
>>> Cell A1 has a number.
>>> Cell B1 is to have how much less, or much more, A1 is than 40.
>>> When A1 is less than 40 the answer in B1 is to be in black.
>>> When A1 is more than 40 the answer in B1 is to be in red.
>>> .
>>> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
>>> is black.
>>>
>>>
>>>
>>>      
>> What you want to do is exactly the purpose of Conditional Formatting. (menu
>> path Format > Conditional Formatting)
>>
>> Look up Conditional Formatting in Help for specific guidance on how to use
>> it. If stuck after doing that, I can walk you through it, but it's not
>> particularly difficult.
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>>    
>
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Gene Young]

Walter Hildebrandt wrote:

> Please seed some guidance on how to get starter.  I have tried various
> combinations in the Conditional Formatting wizard but none of them worked.
>
> On Fri, Nov 6, 2009 at 2:44 PM, Richard Detwiler <RLShadow@...> wrote:
>
>> Walter Hildebrandt wrote:
>>
>>> Cell A1 has a number.
>>> Cell B1 is to have how much less, or much more, A1 is than 40.
>>> When A1 is less than 40 the answer in B1 is to be in black.
>>> When A1 is more than 40 the answer in B1 is to be in red.
>>> .
>>> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
>>> is black.
>>>
>>>
>>>
>> What you want to do is exactly the purpose of Conditional Formatting. (menu
>> path Format > Conditional Formatting)
>>
>> Look up Conditional Formatting in Help for specific guidance on how to use
>> it. If stuck after doing that, I can walk you through it, but it's not
>> particularly difficult.

I had no idea how to do this but it seemed intriguing so I followed
Richard's advice.  I came up with this, and it works;

Insert this formula into B1;

=IF(A1>40;A1-40;A1-40)

Select any cell and format it as a number, text color red.  Use that
cell to create a new cell style (I named mine ConditionRed, name it
whatever will be easy for you to remember.)

Now select cell B1 and from the menu select Format > Conditional
Formatting > put a check in condition 1; in the drop down boxes select
"cell value is"; "greater than"; 40 Select Cell Style "ConditionRed" (or
whatever you named it.) Select OK

You're done.

If A1 is greater than 40, B1 will display a positive number in red.
If A1 is less than 40, B1 will display a negative number in black.

--
Gene Y.

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Brian Barker]

At 22:43 06/11/2009 -0500, Gene Young wrote:
>Insert this formula into B1;
>
>=IF(A1>40;A1-40;A1-40)

Or, more simply, just:
   =A1-40

;^)

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Gene Young]

Gene Young wrote:

> Walter Hildebrandt wrote:
>> Please seed some guidance on how to get starter.  I have tried various
>> combinations in the Conditional Formatting wizard but none of them
>> worked.
>>
>> On Fri, Nov 6, 2009 at 2:44 PM, Richard Detwiler <RLShadow@...>
>> wrote:
>>
>>> Walter Hildebrandt wrote:
>>>
>>>> Cell A1 has a number.
>>>> Cell B1 is to have how much less, or much more, A1 is than 40.
>>>> When A1 is less than 40 the answer in B1 is to be in black.
>>>> When A1 is more than 40 the answer in B1 is to be in red.
>>>> .
>>>> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10
>>>> in B1
>>>> is black.
>>>>
>>>>
>>>>
>>> What you want to do is exactly the purpose of Conditional Formatting.
>>> (menu
>>> path Format > Conditional Formatting)
>>>
>>> Look up Conditional Formatting in Help for specific guidance on how
>>> to use
>>> it. If stuck after doing that, I can walk you through it, but it's not
>>> particularly difficult.
>
> I had no idea how to do this but it seemed intriguing so I followed
> Richard's advice.  I came up with this, and it works;
>
> Insert this formula into B1;
>
> =IF(A1>40;A1-40;A1-40)
>
> Select any cell and format it as a number, text color red.  Use that
> cell to create a new cell style (I named mine ConditionRed, name it
> whatever will be easy for you to remember.)
>
> Now select cell B1 and from the menu select Format > Conditional
> Formatting > put a check in condition 1; in the drop down boxes select
> "cell value is"; "greater than"; 40 Select Cell Style "ConditionRed" (or
> whatever you named it.) Select OK
>
> You're done.
>
> If A1 is greater than 40, B1 will display a positive number in red.
> If A1 is less than 40, B1 will display a negative number in black.
>

Corrections:

Instead of the formula insert A1-40 and in the Conditional formatting
select "greater than or equal to" 0.

For some reason I could not get it to work with out the formula but when
I started from scratch it finally did.  The 40 instead of 0 was just a typo.


--
Gene Y.

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Johnny Rosenberg]

2009/11/6 Walter Hildebrandt <wh2468@...>:
> Cell A1 has a number.
> Cell B1 is to have how much less, or much more, A1 is than 40.
> When A1 is less than 40 the answer in B1 is to be in black.
> When A1 is more than 40 the answer in B1 is to be in red.
> .
> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
> is black.
>

Have you tried the STYLE command?

First create a style with red text, let's call it RedText. If you
don't know how to crate styles, press F11 and experiment a bit from
there. Hint: Right clicking an existing style might be a good start…

Now, in B1, enter your formula:
=A1-40+IF(A1>40;STYLE("RedText");0)

The STYLE function always returns 0, so the formula is mathematically
equal to A1-40+0.

Using conditional formatting will also work. This is just another way
to do the same thing in this case. In some cases conditional
formatting is the better choice, in some cases this solution is the
one to prefer.

A bit OT:
I'll give you an example where the use of STYLE is exceptionally good:
Let's say that you have a cell than can contain some words (maybe
names of people?). Maybe we have a list of when someone will do what.
Maybe we have a certain task to do and we decided that we all would
share the job.
Everyone can take a look on the list to see when he or she is supposed
to do the task.
Well, if every one of us was assigned a specific colour, it would be
easy to see your name in the list, right? So my name maybe will be
displayed on a red background, your with a blue backgound, Richard is
yellow, Gene is green (which is a rhyme, by the way…) and Brian is
Magenta. Well, first we create one style for each person. Give each
style the same name as the person associated with it.

To do this the easy way (maybe there is even easier ways, please tell
me in that case), use one sheet for inputing data and another sheet
for viewing it. Name the sheets, maybe ”Input” and ”View”.
Let's say that we use column A for date and column B for peoples
names. Let's also say the the first row is for headers.

So input a date in A2 and a name in B2 and continue with A3, B3 and so
on. All this is done on the Input sheet, of course.
Now switch to the View sheet.
Copy the headers from the Input Sheet. A good way (I think) to do this
is to just type ”=Input.A1” (without the quotes, of course, like in
the rest of my examples in this message). Now you can copy this cell
where ever you want in the View sheet, and it will display the value
of the corresponding cell on the Input sheet. This way, if you change
your headings or other cells, you only need to change them on the
Input sheet, which is a good thing in most cases.

Ok, now input the following in B2:
=LEFT(Input.B2 & STYLE(Input.B2);LEN(Input.B2))
Highlight B2 (still on the View sheet) and copy it down as far as you need.

Without the LEFT function, Walter would have been turned into Walter0,
for example, and that doesn't look very good…

This way we are not limited to the three conditions of conditional
formatting. If we are 100 people, we ”only” need to create 100 styles
to make it work. The tough thing in this case is maybe to find 100
different colours that doesn't look like each other…

Well, this was only an example and it works (I actually tested it
while I wrote this message).

Johnny Rosenberg

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Johnny Rosenberg]

2009/11/7 Johnny Rosenberg <gurus.knugum@...>:

> 2009/11/6 Walter Hildebrandt <wh2468@...>:
>> Cell A1 has a number.
>> Cell B1 is to have how much less, or much more, A1 is than 40.
>> When A1 is less than 40 the answer in B1 is to be in black.
>> When A1 is more than 40 the answer in B1 is to be in red.
>> .
>> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
>> is black.
>>
>
> Have you tried the STYLE command?
>
> First create a style with red text, let's call it RedText. If you
> don't know how to crate styles, press F11 and experiment a bit from
> there. Hint: Right clicking an existing style might be a good start…
>
> Now, in B1, enter your formula:
> =A1-40+IF(A1>40;STYLE("RedText");0)
>
> The STYLE function always returns 0, so the formula is mathematically
> equal to A1-40+0.
>
> Using conditional formatting will also work. This is just another way
> to do the same thing in this case. In some cases conditional
> formatting is the better choice, in some cases this solution is the
> one to prefer.
>
> A bit OT:
> I'll give you an example where the use of STYLE is exceptionally good:
> Let's say that you have a cell than can contain some words (maybe
> names of people?). Maybe we have a list of when someone will do what.
> Maybe we have a certain task to do and we decided that we all would
> share the job.
> Everyone can take a look on the list to see when he or she is supposed
> to do the task.
> Well, if every one of us was assigned a specific colour, it would be
> easy to see your name in the list, right? So my name maybe will be
> displayed on a red background, your with a blue backgound, Richard is
> yellow, Gene is green (which is a rhyme, by the way…) and Brian is
> Magenta. Well, first we create one style for each person. Give each
> style the same name as the person associated with it.
>
> To do this the easy way (maybe there is even easier ways, please tell
> me in that case), use one sheet for inputing data and another sheet
> for viewing it. Name the sheets, maybe ”Input” and ”View”.
> Let's say that we use column A for date and column B for peoples
> names. Let's also say the the first row is for headers.
>
> So input a date in A2 and a name in B2 and continue with A3, B3 and so
> on. All this is done on the Input sheet, of course.
> Now switch to the View sheet.
> Copy the headers from the Input Sheet. A good way (I think) to do this
> is to just type ”=Input.A1” (without the quotes, of course, like in
> the rest of my examples in this message).

Type this in A1 on the View sheet, of course. Seems like I forgot to
mention that…

Johnny Rosenberg

>Now you can copy this cell
> where ever you want in the View sheet, and it will display the value
> of the corresponding cell on the Input sheet. This way, if you change
> your headings or other cells, you only need to change them on the
> Input sheet, which is a good thing in most cases.
>
> Ok, now input the following in B2:
> =LEFT(Input.B2 & STYLE(Input.B2);LEN(Input.B2))
> Highlight B2 (still on the View sheet) and copy it down as far as you need.
>
> Without the LEFT function, Walter would have been turned into Walter0,
> for example, and that doesn't look very good…
>
> This way we are not limited to the three conditions of conditional
> formatting. If we are 100 people, we ”only” need to create 100 styles
> to make it work. The tough thing in this case is maybe to find 100
> different colours that doesn't look like each other…
>
> Well, this was only an example and it works (I actually tested it
> while I wrote this message).
>
> Johnny Rosenberg
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Walter Hildebrandt-6]

I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and 40 in
cell A3.  Putting the formula

A1-40+IF(A1>40;STYLE("RedText");0)

in cell B1.  I then copied B1 to B2 and to B3.

The correct numbers are in B1, B2, and B3.  B1 is 10, B2 is -10 and B3 is
0.  *The problem is that all the B cells have red numbers.*  I have probably
not crated the correct RedText style

When creating the RedText style, I clicked on the “Font Effects” tab > In
the “Font color” pull-down menu I selected *Red* > I click *OK*.  How do I
get the minus numbers, such as the -10 in B2, to be black instead of red?


On Sat, Nov 7, 2009 at 12:34 AM, Johnny Rosenberg <gurus.knugum@...>wrote:

> 2009/11/7 Johnny Rosenberg <gurus.knugum@...>:
> > 2009/11/6 Walter Hildebrandt <wh2468@...>:
> >> Cell A1 has a number.
> >> Cell B1 is to have how much less, or much more, A1 is than 40.
> >> When A1 is less than 40 the answer in B1 is to be in black.
> >> When A1 is more than 40 the answer in B1 is to be in red.
> >> .
> >> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in
> B1
> >> is black.
> >>
> >
> > Have you tried the STYLE command?
> >
> > First create a style with red text, let's call it RedText. If you
> > don't know how to crate styles, press F11 and experiment a bit from
> > there. Hint: Right clicking an existing style might be a good start…
> >
> > Now, in B1, enter your formula:
> > =A1-40+IF(A1>40;STYLE("RedText");0)
> >
> > The STYLE function always returns 0, so the formula is mathematically
> > equal to A1-40+0.
> >
> > Using conditional formatting will also work. This is just another way
> > to do the same thing in this case. In some cases conditional
> > formatting is the better choice, in some cases this solution is the
> > one to prefer.
> >
> > A bit OT:
> > I'll give you an example where the use of STYLE is exceptionally good:
> > Let's say that you have a cell than can contain some words (maybe
> > names of people?). Maybe we have a list of when someone will do what.
> > Maybe we have a certain task to do and we decided that we all would
> > share the job.
> > Everyone can take a look on the list to see when he or she is supposed
> > to do the task.
> > Well, if every one of us was assigned a specific colour, it would be
> > easy to see your name in the list, right? So my name maybe will be
> > displayed on a red background, your with a blue backgound, Richard is
> > yellow, Gene is green (which is a rhyme, by the way…) and Brian is
> > Magenta. Well, first we create one style for each person. Give each
> > style the same name as the person associated with it.
> >
> > To do this the easy way (maybe there is even easier ways, please tell
> > me in that case), use one sheet for inputing data and another sheet
> > for viewing it. Name the sheets, maybe ”Input” and ”View”.
> > Let's say that we use column A for date and column B for peoples
> > names. Let's also say the the first row is for headers.
> >
> > So input a date in A2 and a name in B2 and continue with A3, B3 and so
> > on. All this is done on the Input sheet, of course.
> > Now switch to the View sheet.
> > Copy the headers from the Input Sheet. A good way (I think) to do this
> > is to just type ”=Input.A1” (without the quotes, of course, like in
> > the rest of my examples in this message).
>
> Type this in A1 on the View sheet, of course. Seems like I forgot to
> mention that…
>
> Johnny Rosenberg
>
>
> >Now you can copy this cell
> > where ever you want in the View sheet, and it will display the value
> > of the corresponding cell on the Input sheet. This way, if you change
> > your headings or other cells, you only need to change them on the
> > Input sheet, which is a good thing in most cases.
> >
> > Ok, now input the following in B2:
> > =LEFT(Input.B2 & STYLE(Input.B2);LEN(Input.B2))
> > Highlight B2 (still on the View sheet) and copy it down as far as you
> need.
> >
> > Without the LEFT function, Walter would have been turned into Walter0,
> > for example, and that doesn't look very good…
> >
> > This way we are not limited to the three conditions of conditional
> > formatting. If we are 100 people, we ”only” need to create 100 styles
> > to make it work. The tough thing in this case is maybe to find 100
> > different colours that doesn't look like each other…
> >
> > Well, this was only an example and it works (I actually tested it
> > while I wrote this message).
> >
> > Johnny Rosenberg
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

[Brian Barker]

At 11:21 07/11/2009 -0700, Walter Hildebrandt wrote:

>I created a test spreadsheet and put 50 in cell A1, 30 in cell A2,
>and 40 in cell A3.  Putting the formula
>
>A1-40+IF(A1>40;STYLE("RedText");0)
>
>in cell B1.  I then copied B1 to B2 and to B3.
>
>The correct numbers are in B1, B2, and B3.  B1 is 10, B2 is -10 and
>B3 is 0.  *The problem is that all the B cells have red numbers.*  I
>have probably not created the correct RedText style

No: your style is probably OK.

>How do I get the minus numbers, such as the -10 in B2, to be black
>instead of red?

I'm not sure why your formula doesn't work: it presumably relates to
how the expression is parsed and evaluated.  But using STYLE(IF())
instead of IF(STYLE()) appears to solve the problem:
   =A1-40+STYLE(IF(A1>40;"RedText"))

Incidentally, you can refer to the current cell value using the
CURRENT() function, so another way to do this is:
   =A1-40+STYLE(IF(CURRENT()>0;"Red"))

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Gene Young]

Walter,
If you are trying to duplicate your original request in subsequent rows,
see below:

Walter Hildebrandt wrote:
> I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and 40 in
> cell A3.  Putting the formula
>
> A1-40+IF(A1>40;STYLE("RedText");0)
> in cell B1.  I then copied B1 to B2 and to B3.

You do not need a formula.  Just enter (for Row 1) A1-40 in Cell B1. For
Row 2 you must enter A2-40 in Cell B2, etc. Copying will not work.

>
> The correct numbers are in B1, B2, and B3.  B1 is 10, B2 is -10 and B3 is
> 0.  *The problem is that all the B cells have red numbers.*  I have probably
> not crated the correct RedText style
>
> When creating the RedText style, I clicked on the “Font Effects” tab > In
> the “Font color” pull-down menu I selected *Red* > I click *OK*.  How do I
> get the minus numbers, such as the -10 in B2, to be black instead of red?
>

This is where conditional formatting comes in, as I described in an
earlier post.

Now select cell B1 (or B2, etc.) and from the menu bar select Format >
Conditional Formatting ; Put a check in condition 1; in the drop down
boxes select "cell value is"; "greater than or equal to"; in the next
box enter 0 (zero); Select Cell Style "RedText" Select OK.  That will work.
You must set the conditional formatting individually for each cell you
want it to apply to.  I just verified the above and it works perfectly


>
>> 2009/11/7 Johnny Rosenberg <gurus.knugum@...>:
>>> 2009/11/6 Walter Hildebrandt <wh2468@...>:
>>>> Cell A1 has a number.
>>>> Cell B1 is to have how much less, or much more, A1 is than 40.
>>>> When A1 is less than 40 the answer in B1 is to be in black.
>>>> When A1 is more than 40 the answer in B1 is to be in red.

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Brian Barker]

At 14:34 07/11/2009 -0500, Gene Young wrote:
>You do not need a formula.

Er, he probably does!

>Just enter (for Row 1) A1-40 in Cell B1.

=A1-40 *is* a formula.

>For Row 2 you must enter A2-40 in Cell B2, etc. Copying will not work.

Oh, it will!  Spreadsheets would be pretty tedious and of limited use
if that were so.  Filling or copying whilst allowing the formula to
be appropriately modified automatically is one of the basic
facilities of a spreadsheet.

>You must set the conditional formatting individually for each cell
>you want it to apply to.

No, that's not true either.  You can either just fill or copy the
conditional formatting along with the cell formulae, or else use the
Format Paintbrush to carry over the conditional formatting by itself.

>I just verified the above and it works perfectly

Indeed it will - and even if you do it slightly more easily.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Walter Hildebrandt-6]

Yes, your formula does work.  The colors are correct.

I added the following at the front of your formula and that  works.

IF(ISBLANK(A1);"Empty";IF(A1=0;"zero";

I am pushing my luck but there is another change I would like to make.  The
numbers in the A column are formatted as Numbers but they are actually
percent numbers.  Is there a way for the numbers in column B to have the %
signed included?  The B column has just the number without a % sign.

On Sat, Nov 7, 2009 at 12:10 PM, Brian Barker <b.m.barker@...>wrote:

> At 11:21 07/11/2009 -0700, Walter Hildebrandt wrote:
>
>> I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and 40
>> in cell A3.  Putting the formula
>>
>> A1-40+IF(A1>40;STYLE("RedText");0)
>>
>> in cell B1.  I then copied B1 to B2 and to B3.
>>
>> The correct numbers are in B1, B2, and B3.  B1 is 10, B2 is -10 and B3 is
>> 0.  *The problem is that all the B cells have red numbers.*  I have probably
>> not created the correct RedText style
>>
>
> No: your style is probably OK.
>
>
>  How do I get the minus numbers, such as the -10 in B2, to be black instead
>> of red?
>>
>
> I'm not sure why your formula doesn't work: it presumably relates to how
> the expression is parsed and evaluated.  But using STYLE(IF()) instead of
> IF(STYLE()) appears to solve the problem:
>  =A1-40+STYLE(IF(A1>40;"RedText"))
>
> Incidentally, you can refer to the current cell value using the CURRENT()
> function, so another way to do this is:
>  =A1-40+STYLE(IF(CURRENT()>0;"Red"))
>
> I trust this helps.
>
> Brian Barker
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

[Johnny Rosenberg]

2009/11/7 Walter Hildebrandt <wh2468@...>:

> I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and 40 in
> cell A3.  Putting the formula
>
> A1-40+IF(A1>40;STYLE("RedText");0)
>
> in cell B1.  I then copied B1 to B2 and to B3.
>
> The correct numbers are in B1, B2, and B3.  B1 is 10, B2 is -10 and B3 is
> 0.  *The problem is that all the B cells have red numbers.*  I have probably
> not crated the correct RedText style

I tried it when I replied to you and it worked. I am not sure why it
doesn't for you.
Maybe you messed things up when creating the style?

Did you somehow change your default style to display red numbers? F11
→ Right click Default → Click modify → Click Font effects tab. Is the
font colour set to red? If so, change it back to Automatic.

Does it work now?
If not, highlight the cells that contains the style formula. Right
click one of the cells → Format cells… → Font effects. Is the font
colour set to red? Change it back to Automatic.

Does it stil not work? Please send me the whole spreadsheet, or a
similar one that doesn't work, and I will try to debug it for you.

Johnny Rosenberg

>
> When creating the RedText style, I clicked on the “Font Effects” tab > In
> the “Font color” pull-down menu I selected *Red* > I click *OK*.  How do I
> get the minus numbers, such as the -10 in B2, to be black instead of red?
>
>
> On Sat, Nov 7, 2009 at 12:34 AM, Johnny Rosenberg <gurus.knugum@...>wrote:
>
>> 2009/11/7 Johnny Rosenberg <gurus.knugum@...>:
>> > 2009/11/6 Walter Hildebrandt <wh2468@...>:
>> >> Cell A1 has a number.
>> >> Cell B1 is to have how much less, or much more, A1 is than 40.
>> >> When A1 is less than 40 the answer in B1 is to be in black.
>> >> When A1 is more than 40 the answer in B1 is to be in red.
>> >> .
>> >> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in
>> B1
>> >> is black.
>> >>
>> >
>> > Have you tried the STYLE command?
>> >
>> > First create a style with red text, let's call it RedText. If you
>> > don't know how to crate styles, press F11 and experiment a bit from
>> > there. Hint: Right clicking an existing style might be a good start…
>> >
>> > Now, in B1, enter your formula:
>> > =A1-40+IF(A1>40;STYLE("RedText");0)
>> >
>> > The STYLE function always returns 0, so the formula is mathematically
>> > equal to A1-40+0.
>> >
>> > Using conditional formatting will also work. This is just another way
>> > to do the same thing in this case. In some cases conditional
>> > formatting is the better choice, in some cases this solution is the
>> > one to prefer.
>> >
>> > A bit OT:
>> > I'll give you an example where the use of STYLE is exceptionally good:
>> > Let's say that you have a cell than can contain some words (maybe
>> > names of people?). Maybe we have a list of when someone will do what.
>> > Maybe we have a certain task to do and we decided that we all would
>> > share the job.
>> > Everyone can take a look on the list to see when he or she is supposed
>> > to do the task.
>> > Well, if every one of us was assigned a specific colour, it would be
>> > easy to see your name in the list, right? So my name maybe will be
>> > displayed on a red background, your with a blue backgound, Richard is
>> > yellow, Gene is green (which is a rhyme, by the way…) and Brian is
>> > Magenta. Well, first we create one style for each person. Give each
>> > style the same name as the person associated with it.
>> >
>> > To do this the easy way (maybe there is even easier ways, please tell
>> > me in that case), use one sheet for inputing data and another sheet
>> > for viewing it. Name the sheets, maybe ”Input” and ”View”.
>> > Let's say that we use column A for date and column B for peoples
>> > names. Let's also say the the first row is for headers.
>> >
>> > So input a date in A2 and a name in B2 and continue with A3, B3 and so
>> > on. All this is done on the Input sheet, of course.
>> > Now switch to the View sheet.
>> > Copy the headers from the Input Sheet. A good way (I think) to do this
>> > is to just type ”=Input.A1” (without the quotes, of course, like in
>> > the rest of my examples in this message).
>>
>> Type this in A1 on the View sheet, of course. Seems like I forgot to
>> mention that…
>>
>> Johnny Rosenberg
>>
>>
>> >Now you can copy this cell
>> > where ever you want in the View sheet, and it will display the value
>> > of the corresponding cell on the Input sheet. This way, if you change
>> > your headings or other cells, you only need to change them on the
>> > Input sheet, which is a good thing in most cases.
>> >
>> > Ok, now input the following in B2:
>> > =LEFT(Input.B2 & STYLE(Input.B2);LEN(Input.B2))
>> > Highlight B2 (still on the View sheet) and copy it down as far as you
>> need.
>> >
>> > Without the LEFT function, Walter would have been turned into Walter0,
>> > for example, and that doesn't look very good…
>> >
>> > This way we are not limited to the three conditions of conditional
>> > formatting. If we are 100 people, we ”only” need to create 100 styles
>> > to make it work. The tough thing in this case is maybe to find 100
>> > different colours that doesn't look like each other…
>> >
>> > Well, this was only an example and it works (I actually tested it
>> > while I wrote this message).
>> >
>> > Johnny Rosenberg
>> >
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Johnny Rosenberg]

2009/11/7 Walter Hildebrandt <wh2468@...>:

> Yes, your formula does work.  The colors are correct.
>
> I added the following at the front of your formula and that  works.
>
> IF(ISBLANK(A1);"Empty";IF(A1=0;"zero";
>
> I am pushing my luck but there is another change I would like to make.  The
> numbers in the A column are formatted as Numbers but they are actually
> percent numbers.  Is there a way for the numbers in column B to have the %
> signed included?  The B column has just the number without a % sign.

Have you tried the % button or format it as %? That will convert your
data to %, so 40 will be 4000,00%. Maybe you don't want that. A dirty
workaround in that case could be Right click the cell or cell range →
Format cells… → 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.

Johnny Rosenberg

>
> On Sat, Nov 7, 2009 at 12:10 PM, Brian Barker <b.m.barker@...>wrote:
>
>> At 11:21 07/11/2009 -0700, Walter Hildebrandt wrote:
>>
>>> I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and 40
>>> in cell A3.  Putting the formula
>>>
>>> A1-40+IF(A1>40;STYLE("RedText");0)
>>>
>>> in cell B1.  I then copied B1 to B2 and to B3.
>>>
>>> The correct numbers are in B1, B2, and B3.  B1 is 10, B2 is -10 and B3 is
>>> 0.  *The problem is that all the B cells have red numbers.*  I have probably
>>> not created the correct RedText style
>>>
>>
>> No: your style is probably OK.
>>
>>
>>  How do I get the minus numbers, such as the -10 in B2, to be black instead
>>> of red?
>>>
>>
>> I'm not sure why your formula doesn't work: it presumably relates to how
>> the expression is parsed and evaluated.  But using STYLE(IF()) instead of
>> IF(STYLE()) appears to solve the problem:
>>  =A1-40+STYLE(IF(A1>40;"RedText"))
>>
>> Incidentally, you can refer to the current cell value using the CURRENT()
>> function, so another way to do this is:
>>  =A1-40+STYLE(IF(CURRENT()>0;"Red"))
>>
>> I trust this helps.
>>
>> Brian Barker
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[TomW-3]

Walter Hildebrandt wrote:
> Cell A1 has a number.
> Cell B1 is to have how much less, or much more, A1 is than 40.
> When A1 is less than 40 the answer in B1 is to be in black.
> When A1 is more than 40 the answer in B1 is to be in red.
> .
> This means if A1 is 50, the 10 in B1 is red.  When A1 is 30, the -10 in B1
> is black.
>
>  
Walter:

Another option is just to create a new number format.  The following
format code will turn the font red to anything equal to or greater than
0. Anything less than zero will be black.

[RED][>0]0;[BLACK]0

Search for 'number formats;codes' in the help file for more information
on user-defined number formats.

TomW

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Walter Hildebrandt-6]

I had deleted the spreadsheet based on what you had suggested.  I am now
using the following formula on the spreadsheet;

IF(ISBLANK(A1);"Empty";IF(A1=0;"zero";A1-40+STYLE(IF(A1>40;"RedText"))))

I put 0"%" in the Format field:    The % is now included in column B.  The %
makes the cells easier to understand.  Also the text (Empty and Zero) is now
red.  The text had been black.

The spreadsheets is color coded.  By color coded I mean the spreadsheet can
be quickly read and the cells that are red can easily be differentiated from
the cells that are black.

In some cases a negative number is good while in other cases a negative
number is bad.  When a
negative number is good it now appears in black.  When a negative number is
bad it now appears in red.

This is also a minor improvement.   A cell with a red Empty is not good in
that there is no data in the cell.  A red cell with 0 in it is either
meaningless or bad.

Between you and the others, a great job was done in solving my problems.

Walter

On Sat, Nov 7, 2009 at 2:06 PM, Johnny Rosenberg <gurus.knugum@...>wrote:

> 2009/11/7 Walter Hildebrandt <wh2468@...>:
> > Yes, your formula does work.  The colors are correct.
> >
> > I added the following at the front of your formula and that  works.
> >
> > IF(ISBLANK(A1);"Empty";IF(A1=0;"zero";
> >
> > I am pushing my luck but there is another change I would like to make.
>  The
> > numbers in the A column are formatted as Numbers but they are actually
> > percent numbers.  Is there a way for the numbers in column B to have the
> %
> > signed included?  The B column has just the number without a % sign.
>
> Have you tried the % button or format it as %? That will convert your
> data to %, so 40 will be 4000,00%. Maybe you don't want that. A dirty
> workaround in that case could be Right click the cell or cell range →
> Format cells… → 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.
>
> Johnny Rosenberg
>
>
> >
> > On Sat, Nov 7, 2009 at 12:10 PM, Brian Barker <b.m.barker@...
> >wrote:
> >
> >> At 11:21 07/11/2009 -0700, Walter Hildebrandt wrote:
> >>
> >>> I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and
> 40
> >>> in cell A3.  Putting the formula
> >>>
> >>> A1-40+IF(A1>40;STYLE("RedText");0)
> >>>
> >>> in cell B1.  I then copied B1 to B2 and to B3.
> >>>
> >>> The correct numbers are in B1, B2, and B3.  B1 is 10, B2 is -10 and B3
> is
> >>> 0.  *The problem is that all the B cells have red numbers.*  I have
> probably
> >>> not created the correct RedText style
> >>>
> >>
> >> No: your style is probably OK.
> >>
> >>
> >>  How do I get the minus numbers, such as the -10 in B2, to be black
> instead
> >>> of red?
> >>>
> >>
> >> I'm not sure why your formula doesn't work: it presumably relates to how
> >> the expression is parsed and evaluated.  But using STYLE(IF()) instead
> of
> >> IF(STYLE()) appears to solve the problem:
> >>  =A1-40+STYLE(IF(A1>40;"RedText"))
> >>
> >> Incidentally, you can refer to the current cell value using the
> CURRENT()
> >> function, so another way to do this is:
> >>  =A1-40+STYLE(IF(CURRENT()>0;"Red"))
> >>
> >> I trust this helps.
> >>
> >> Brian Barker
> >>
> >>
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe, e-mail: users-unsubscribe@...
> >> For additional commands, e-mail: users-help@...
> >>
> >>
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>