result below zero should be in color red

[jv]

Good day to all,

I need your assistance concerning my calculations

1 - result below zero should be in color red, can this be
possible in way of formula format.
2 - result in zero should be blank or '-' symbol, also in
formula format.

thank you.

jv

 

[GB]

You can do this with conditional formatting.

 

[jv]

Hi GB,

Ive checked the help for conditional formatting but i cant
understand it. Im using excel 2000. can you show me how
using a formula in column 'c'.

Here is my sample.
column: A B C
( Budget ) ( Expenses ) ( Result )

1 - 70,000 95,000 -25,000
2 - 70,000 70,000 0
3 - 47,000 35,000 12,000

In the above sample, If the result in column 'c' is :

1 - overbudget, the figure in 'c' should appear in RED
color.
2 - zero, it should show a BLANK CELL or '-' sign.
3 - less than the budget amount, it should appear in BLUE
color or if possible it will HIGHLIGHT entire cell.

 

[Bob Phillips]

Select C1
Menu Format>Conditional Formatting
Change operation dropdown box to 'Less than'
Put 0 in value box
Click format button
On Font tab, choose colour of red
OK
Click Add>> button
Change operation to equal to
Put 0 in value box
Click format button
On Font tab, choose colour of white
OK
OK

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

You can do the first two with regular formatting:

Format/Cells/Number/Custom: #,##0;[Red]-#,##0;"-";@

XL has four fields to its format code. By default the first is for
positive numbers, the second for negative numbers, the third for
zero and the fourth for text.

You can use Conditional formatting for the third criterion. Select
Column C (or your range), with C1 the active cell. Choose
Format/Conditional Formatting... and enter

CF1: Formula is =($B1<$A1)

Click format, select the Patterns tab, choose your color and click
OK, OK.

 

[jv]

JE,

Thank you so much for your assistance, it helps alot.

One last question.

in my question item # 2 - in case the '-' (zero)value
should be color in blue how can i do that? disregarding
the other query.

jv

 

[jv]

Bob,

Thank you for the assistance, it really helps alot.

jv

 

[J.E. McGimpsey]

Format/Cells/Number/Custom #,##0;[Red]#,##0;[Blue]-;@

 

[ed]

You can do this with conditional formatting.

Format your numbers to be red instead of black. Format
Menu/Cells/number or Custom. ed

 

[Ann Scharpf]

What you want to do is use the Format / Conditional
Formatting in combination with Tools / Options.

1. Select the cells in Column C
2. Format / Conditional Formatting
3. Add the negative condition:
Cell value is, less than, 0 (zero)
Click the format button, select font = red
Click the Add button
4. Add the positive condition:
Cell value is, greater than, 0 (zero)
Click the format button, select pattern = blue
Click the Add button
5. Hide zero values:
Tools / Options / View / Uncheck "zero values"

HTH,

Ann