Mid Function Conditional Formatting - Unusual Behavior

[ovm512]

Hi to all,

I am a teacher attempting to perform some magic apparently beyond my abilities.
The goal is simple: isolate numeric data found within parentheses in a text string via a formula to then highlight a cell presenting said data within a range. Here is what I have so far:

Excel 2016 (Mac) 32 bit

	A	B	C
C (80%)

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.8[/TD]
[TD="align: right"]TRUE[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B2[/TH]
[TD="align: left"]=MID([COLOR=rgb(255]A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1[/COLOR])+0[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C2[/TH]
[TD="align: left"]=([COLOR=rgb(255]MID(A2,SEARCH([COLOR=0)]"(",A2[/COLOR])+1,SEARCH([COLOR=0)]")",A2[/COLOR])-SEARCH([COLOR=0)]"(",A2[/COLOR])-1)+0[/COLOR])>=0.8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

My use of the MID formula tests well, presenting TRUE in C2, and this I thought was what I needed to apply conditional formatting rules for the entire sheet. To be successful, the formula in C2 should provide the right formatting rule to pick out the 80 in A2 and so highlight A2.

However, what doesn't appear in the above sample is the highlighted cell resulting from pasting the formula in C2 into a formatting rule (a rule created with the entire sheet selected, FYI). Such a rule highlights B1 and only B1 (!). [Side note, I used the Forum Tools Add In to insert my cells into this post, but it didn't capture the formatting.] So, my formula fails to pick out target data in A2 and results in a seemingly random, empty cell being highlighted. Any help would be greatly appreciated. Thanks in advance.

 

[steve the fish]

When you use CF formula the formula should reference the top left cell of the range its being applied to. Your formula would work if being applied to A2:A10 for example.

 

[jtakw]

Hi,

If you're selecting the Entire sheet by clicking on the upper left triangle, you need to change your formula to reference A1 (rather than A2):

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)+0>=0.8

 

[ovm512]

When you use CF formula the formula should reference the top left cell of the range its being applied to. Your formula would work if being applied to A2:A10 for example.

Hiya,

Thanks for the response. This is the formatting formula that results in misapplied conditional formats:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Lucida Grande'; color: #000000}</style>=(MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1)+0)>=0.8

So I would think I would be ok based on your concern, and yet I get the blank B1 cell highlighted by this formula. Any other ideas?

 

[ovm512]

Hi,

If you're selecting the Entire sheet by clicking on the upper left triangle, you need to change your formula to reference A1 (rather than A2):

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)+0>=0.8

Gotcha,

Now my formula highlights B2, which makes more sense, but it still cannot seem to find the numeric data ("80") in A2, I suppose, as it does not highlight the desired cell. Nevertheless, a major improvement, and thank you!
Why do you think my main goal [having the formula parse the text string in A1, "+C (80%)", to return just the numeric value ("80"), thus highlighting it] has yet to be achieved?

 

[steve the fish]

The formula you provided would need to look at the C(80%) cell not the cell showing 80. If you want it to look at the cell showing 80 then just use =B1>=80. Apply to appropriate range. Dont use CF on entire sheet.

 

[jtakw]

Your CF formula (if applied correctly), should Not highlight Any cell that does Not contain 80% or more within brackets (i.e. (80%), (81%), (90.5%), etc.).
Any cell that is Empty, Blank, or contain any Other Text or Number should Not be highlighted.

Now, specifically, what "Range" are you trying to highlight using CF,
And, what are the different possible data within this range?

 

[ovm512]

Your CF formula (if applied correctly), should Not highlight Any cell that does Not contain 80% or more within brackets (i.e. (80%), (81%), (90.5%), etc.).
Any cell that is Empty, Blank, or contain any Other Text or Number should Not be highlighted.

Now, specifically, what "Range" are you trying to highlight using CF,
And, what are the different possible data within this range?

Agreed to all of the above, I have selected the entire sheet as my range. Yet I get the B2 result. This or some fairly broad range of rows would be necessary to be successful/useful. Is there any reason why I should not do so? I would wonder why as I need not do this for any other conditional formatting I have used.

To be clear the following is the only data on my test sheet at row 2 (I'm using text as the formatting on the tool is problematic:
A B C
1
2 C (80%) .8 TRUE
[TABLE="width: 195"]
<colgroup><col span="3"></colgroup><tbody></tbody>[/TABLE]

B2 is the only cell being formatted even though the formula used is:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Lucida Grande'; color: #000000}</style>=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)+0>=0.8

And so should, in theory result in a highlight of A1. Thanks again for all of your help.

 

[ovm512]

And so should, in theory result in a highlight of A1. Thanks again for all of your help.

"A1" above should read A2 sorry and thank you again.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Lucida Grande'; color: #000000}</style>