Conditional Formatting with Concatenated formula

[chubbchubb]

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

=CONCATENATE("Gap"," ",(SUM(VLOOKUP("Not Disadvantaged",Y11spring,4)-O9)))

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Term 1[/TD]
[TD]Term 2[/TD]
[TD]Term 2v2[/TD]
[/TR]
[TR]
[TD]Gap 12.14[/TD]
[TD]Gap 9.13[/TD]
[TD]9.13[/TD]
[/TR]
[TR]
[TD]Gap 31.94[/TD]
[TD]Gap 21.74[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gap 9.34[/TD]
[TD]Gap 12.61[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Special-K99]

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

 

[chubbchubb]

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?

 

[Special-K99]

No idea.

 

[chubbchubb]

fair enough, thank you

 

[steve the fish]

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]

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

 

[steve the fish]

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]

i see, oh well it is sorted now, thanks anyway