Conditional formatting after concatenate fails

0
votes

I'm using the function concatenate to show two values in one cell (the sheet shows the amount present ("X") and the amount required ("Y"), presenting a concatenated value of X / Y). X is calculated and Y is a fixed value in another sheet.

I want to make two events visible by using conditional formatting: when X is equal to Y (background color needs to turn green) and when X is higher than Y (background color needs to turn red). Values range between 0 and 99.

This is how the sheet currently looks.

The current conditional format is strictly text based "1 / 1" makes it green, "2 / 1" makes it red and so on which is very ineffective.

Is there a way that I can use one conditional rule per event? I've been struggling with rules per cell (made a comparison between the calculated value and the fixed value); but this meant I had to write this formula for every calculation. With over 300 calculated cells this was a pain to do.

I bet there's some smart solution I'm missing :-)

2
excelconcatenationconditional-formatting

2 Answers

1
votes

Thank you very much! I had some puzzling to do due to language differences in Excel and learned some new functionality during my search :)

For me it ended up looking like:

=--DEEL(N6;1;VIND.ALLES(" /";N6;1)-1)>--DEEL(N6;VIND.ALLES("/ ";N6)+LENGTE("/ ");3)

and

=--DEEL(N6;1;VIND.ALLES(" /";N6;1)-1)=--DEEL(N6;VIND.ALLES("/ ";N6)+LENGTE("/ ");3)

At first I thought it would be a problem that my formula used N6 as the first cell to work from, but when applied to the range of cells it works perfectly.

0
votes

Perhaps a rule based on a formula something like the following for the "green" condition:

=--LEFT(A1,FIND(" /",A1))=--MID(A1,FIND("/ ",A1)+LEN("/ "),99)

and a similar one for the "red" condition, changing the middle = to >.

enter image description here