# Conditional Formatting with Concatenated formula

#### chubbchubb

##### New Member
I am hoping someone can help me as I am losing the will to live.
I am using conditional formatting to determine whether a gap has gone up or down from the previous term. It is mostly working but for some reason on a few of the data cells the wrong colour is given.

I am using the formula

which for 99% of the time works, but on a few cells it doesn't. However if you remove the concatenate part of the formula the right colour is then given in the conditional formatting??

For example

 Term 1 Term 2 Term 2v2 Gap 12.14 Gap 9.13 9.13 Gap 31.94 Gap 21.74 Gap 9.34 Gap 12.61

<tbody>
</tbody>

In this instance I would expect on Line 1 the Term 2 cell to turn green as the gap has gone down, but it turns red, but if I remove the concatenate from the formula (Term 2v2) it turns green as it should.
Line 2 works as expected and turns green
I also have the opposite happening, Line 3 should turn red but turns green but if I remove the concatenate from Term 1 it works fine and turns red.

Does the word 'Gap' add value to a cell to make it seem like a bigger number? but then if so why doesn't it cause the same error on all the rows?

Does anyone know why this is happening? All the cells have the same formula (just looking at different columns/cells for the calculation).

TIA

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Special-K99

##### Well-known Member
1. Make sure your data is concistent. Insert "Gap " into C2.

2.
Select A2:C4

Conditional Formatting
New Rule
Use a formula to determine...

=RIGHT(B2,LEN(B2)-4)+0>RIGHT(A2,LEN(A2)-4)+0 format as green

=RIGHT(B2,LEN(B2)-4)+0<RIGHT(A2,LEN(A2)-4)+0 format as red

Last edited:

#### chubbchubb

##### New Member
I gave this a go and the boxes turned
Red
Red
Green

Which I assume is right as B2 is less than A2 so should turn red
B4 is greater than A4 so should turn green

any idea why it didn't work before?

Last edited:

No idea.

#### chubbchubb

##### New Member

fair enough, thank you

#### steve the fish

##### Well-known Member
Why not type these into A1 and B1:

Gap 12.14
Gap 9.13

Then test using:

=A1>B1

They are not numbers so arent treated as such.

#### chubbchubb

##### New Member

Why not type these into A1 and B1:

Gap 12.14
Gap 9.13

Then test using:

=A1>B1

They are not numbers so arent treated as such.

It gives a result of false, it's like it is seeing the smaller number as a the larger number somehow? But if you remove the word GAP it says true

Last edited:

#### steve the fish

##### Well-known Member
No, because its an alphanumeric value excel is not seeing the 'number' on the end as a number. What it is actually testing in this case is if 1 is greater than 9 hence FALSE.

#### chubbchubb

##### New Member
i see, oh well it is sorted now, thanks anyway

Replies
3
Views
37
Replies
7
Views
71
Replies
2
Views
55
Replies
7
Views
69
Replies
3
Views
33