Ask Your Question
1

Need to amend a formula to show negative numbers in red?

asked 2020-09-09 15:43:07 +0100

MrHoggLegg gravatar image

updated 2020-09-09 15:44:40 +0100

First of all, I need to apologize to JohnSun and erAck for answering a question back on March 17th. Thank You Both for answering my question, I should have Thanked You Back Then! May God Bless You Both!

I have a check register that shows negative numbers in black. The formula is as follows:

[>0]" $"* #,##0.00" ";[<0]" $"* (#,##0.00);" $"* -#" ";" "@" "

How can I amend this to return negative amounts in red? Thanks. May God Bless You All! ✝️

edit retag flag offensive close merge delete

Comments

1

We are always happy to help. Just remember for future reference: The easiest way to express gratitude is to mark the answer to your question as correct.

ChekAnswerAsCorrect.gif

JohnSUN gravatar imageJohnSUN ( 2020-09-09 16:34:59 +0100 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2020-09-09 16:07:27 +0100

updated 2020-09-09 16:09:41 +0100

Hello,

you could use the Conditional Formating under

Formating > Conditional > Condition....

Here you can use the Error Style (or any other style you like), wenn the cell value is less than zero.

Conditional Formatting

Hope that helps.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

Have a nice day and let's (continue to) "Be excellent to each other!"

edit flag offensive delete link more
1

answered 2020-09-09 18:37:53 +0100

newbie-02 gravatar image

'kiss' answer (keep it simple and stupid):

try this: [>0]" $"* #,##000" ";[<0][ROT]" $"* (-#,##000);" $"* -#" ";" "@" "

observe that i added a '-' sign, delete if not wanted,

probably you need to replace '[ROT]' - german by '[RED]' - english,

have fun

P.S. 'solved marks' and 'likes' welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the "^" above it if you 'like' the answer,
"v" if you don't,
do not! use 'answer' to add info to your question, either edit the question or add a comment,
'answer' only if you found a solution yourself ...

edit flag offensive delete link more
1

answered 2020-09-09 16:06:31 +0100

JohnSUN gravatar image

Let's see what the Number Format Codes Help says:

UserFormatCode.png

The first paragraph shows that we can set the color very easily with [RED]. The second paragraph explains that there is no need to set the conditions [> 0] and [<0] - it is enough to know that the first part of the format code will be applied to positive values, and the second to negative ones.

The easiest, fastest and most reliable way to solve your problem: press Ctrl+1 to open the Format - Cells - Numbers tab and check the Negative numbers in red

NegativeFormat.png

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-09 15:43:07 +0100

Seen: 34 times

Last updated: Sep 09