negative = red and brackets

[dodda]

I'm a very basic excel user, this is probably an easy one for you guys.

for example, the cells in column A contain numbers with some cells having negative numbers eg 100, 99, -12, 36 etc

the last cell in the column shows the SUM of the column.

My question is, when the SUM of the column is a negative eg -376

I want it to turn the cell red and also put the number into brackets (parentheses) eg (-376)

Thats it, help please
Dodda

 

[jeffreybrown]

Hi Dodda and welcome to the board,

How about a custom format...

;[Red](-0);

 

[dodda]

Thanks for very quick reply

like I said I'm a very basic user

how do I use the custom formula ?

Thanks again
Dodda

 

[jeffreybrown]

Put the cursor on the cell you want to change...

Select Ctrl + 1 which opens the Format cells dialogue box...

On the Number tab select Custom at the bottom of the list...

Under Type: enter the formula provided

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=414

 

[dodda]

Hi Jeff
OK I put in the custom format but it returns red and minus for all sums
eg 100+100+100 = (-300)

I only want the SUM to turn red if the total for the column is a negative
this also will put that negative number into parentheses

eg 100+100-300 = (-100)

Thanks again
Dodda

 

[jeffreybrown]

How about...

0;[Red](-0);

 

[PaddyD]

or:

$#,##0.00;[Red](-$#,##0.00)

 

[leigao84]

Well if you have Excel 07+
Select the cells. Change format to currency, select the one with parentheses and red, and viola.

 

[dodda]

Thanks to you all

PaddyD I shall use your solution I have amended it by taking out the $ and the - as I have been informed that we dont want to show a minus sign as that is what the red and brackets do for us.

We dont need to show it as a currency (anyway we use £ here)

Thats it guys, a speedy resolve to my headache.

Once again Thanks to you all
Dodda

 

[dodda]

Jeff sorry

it hadn't registered with me that you gave a second solution

and IT WORKS

I now have a choice and choice is good !

Thanks
Dodda