conditional format on cells with result of formula

[Bandito1]

Hi all,

Tried to look it up but couldn't find it, so i try it here.
I'm trying to give percentages a color.
Everything >0% should turn green
Everything <0% should turn red

It works when the percentage is small (like -0,65%). But when it's -2.56% for example it just turns green..

I used conditional format; Cell value <00.00 - red and other rule Cell value >00.00 green.

What is going wrong here?

 

[AhoyNC]

Use relative cell references instead of absolute references (remove the "$" from the cell reference).

Book4
	G
2	2
3	-0.2
4	0.025
5	-1.5
6
7
8
9
10
11
12
13
14
15
16
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G16	Expression	=G2:G16>0	text	NO
G2:G16	Expression	=G2:G16<0	text	NO

 

[Alex Blakenburg]

Conditional Formatting is quite a drain on performance. If that is all you are trying to do any reason you don't just use custom formatting ?
0.00%;[Red]-0.00%;0.00%

 

[Bandito1]

Thanks for your reply

Is it possible with custom formatting to display everything above 0.00% green and everything below 0.00% red?

I tried 0,00%;[Red]-0,00%;0,00% in format cells -> custom but nothing changes

 

[Alex Blakenburg]

That's odd. They are definitely numbers right ? ie if you change the format to General the display changes doesn't it.
Assuming it does have a look at the heading "Colors" in this article.

Custom Number Format in Excel

Excel has many built-in formats that you can use: Currency, Accounting, Date, Time, Percentage, etc. If you cannot find the right format, you can create a custom number format.

www.excel-easy.com

 

[Joe4]

What exactly is the formula in these cells that are calculating the percentages?
Can you please post it?

Are you, per chance, using the TEXT function in your formula?
If so, that is returning a TEXT value and not a numeric one, and formatting won't work on it.

 

[Bandito1]

The data comes from a table with;

=VLOOKUP(Table5[@Symbool];Table_0__2;4;FALSE)

Table 0 has stock market data that is imported from google sheets.

 

[Joe4]

Table 0 has stock market data that is imported from google sheets.

I am guessing that the field you are returning back may be a text field (numbers entered as text).
In order to get formatting to be applied, we need to convert it to numbers.

See if making this change to your formula does that:

=VLOOKUP(Table5[@Symbool];Table_0__2;4;FALSE) + 0

 

[Bandito1]

When i make that change the percentage changes in number; 1,00% changes to 0,01.
I would like to see 1,00%

 

[Joe4]

When i make that change the percentage changes in number; 1,00% changes to 0,01.
I would like to see 1,00%

That is exactly correct. 1% = 0.01 (or 0,01 in your case).
Now that this value is numeric, you can now apply the Custom Format to the cell that were discussed in the earlier replies.