Conditional Formatting with Concatenated formula

chubbchubb

New Member
Joined
Sep 9, 2014
Messages
26
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

Term 1Term 2Term 2v2
Gap 12.14Gap 9.139.13
Gap 31.94Gap 21.74
Gap 9.34Gap 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
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Sep 9, 2014
Messages
26
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:

chubbchubb

New Member
Joined
Sep 9, 2014
Messages
26

ADVERTISEMENT

fair enough, thank you
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 9, 2014
Messages
26

ADVERTISEMENT

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
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top