Apply Conditional Formatting based on multiple conditions

Question

I'm attempting to highlight a cell based on two conditions, if the Work Type is equal to (blank), and if the cell itself is greater than 15%, then I want the cell under % Total Work highlighted. My sheet looks like this:

enter image description here

The only way I can think to do this is using the AND function, something like =AND($B:$B="(blank)";$D:$D>.15), but this produces an error. I'm very new to conditional formatting, so I may be missing something obvious. I'm using Excel 2010.

barry houdini · Accepted Answer · 2013-08-06 19:58:48Z

7

You have to apply the formula that applies to the first cell in the range, so if you select the range D8:D100 or similar (where data starts at row 8) then apply this formula in conditional formatting

=AND(B8="(blank)";D8>0.15)

That should do what you need

answered Aug 6, 2013 at 19:58

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ngwilliams Over a year ago

Thanks for your help @barry and @Harrison, I have a follow up question. It turns out the % Total Work column isn't actually in percentage format, but rather a string generated by some more advanced back-end scripts. Is there a way to compare that integer value to 15? I tried =AND(B8="(blank)";MID(D8,1,5)>15), to try to extract just the number, but to no avail. I chose a length of 5 for the substring because the percentage value is actually longer than it appears, its just rounding. Thanks.

barry houdini Over a year ago

Try getting everything before % sign like this =AND(B8="(blank)",LEFT(D8,FIND("%",D8)-1)+0>15)

Collectives™ on Stack Overflow

Apply Conditional Formatting based on multiple conditions

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related