Custom formating question

[robgill205]

Hi,

I have applied custom formatting to a column that looks like this [Color10]0%"  ▲";[Red]-0%"  ▼";

This shows that if the variance in the cell is positive or negative and shows a percentage value next to the symbol. I'm wondering if it's possible to add the criteria where this symbol "─" is shown when the variance is only +-100,000. 

Would this need to be conditional formatting and not custom formatting?

Thanks

[#2 · Accepted solution]

@robgill205 

I'd use conditional formatting.

Select the column; the first cell in the selection should be the active cell. I'll assume it is D2 in the following.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ABS(D2)<100000

or if you meant 100000%, use

=ABS(D2)<1000

Click Format...
Activate the Number tab.
Select Custom.

In the Type box, enter the formula

"─";"─"

Click OK, then click OK again.

[#3]

Thank you, this shows the "─" symbol for all the variances +-€100,000.
Would it be possible to only show the "─" symbol when the variance is negative?
This way my "▲" symbol shows even when the variance is a positive and less than 100,000.

[#4]

@robgill205 

Sure: change the conditional formatting formula to

=AND(D2<0,D2>-100000)