• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting question..

Eloise T

Eloise T

Active Member
Please see attached Excel 2007 file.

Columns B (work order) and C (dollar amount) are received from the buyer and pasted into the spreadsheet.

Columns E (work order) and L (dollar amount) represent what was billed and should match what is in Columns B and C. See formula from Row 7:
{=IF(E7<>"",IF(ISNUMBER(MATCH(TRIM(E7)&TRIM(L7),TRIM($B$7:$B$257)&TRIM($C$7:$C257),0)),"","√"),"--")}

When the dollar amounts don't match, the formula propagated in Column M triggers a red check mark to appear in Column M. I highlighted in yellow by hand the dollar amounts that didn't match for ease of view.


The last conditional format in the Conditional Format list (see illustration below circled in black)...

upload_2017-2-21_13-2-42.png


...tells the array =$M$7:$M$257 to make the check mark red.

What I need is for the check mark to be green if Column C is greater than Column L

and red if Column L is greater than Colum C...i.e. if the opposite is true.


The question is: How do I program Conditional Formatting to:

If =$Mx="√" is true AND Cx >Lx is true, make the check mark green? (where x=cell number)

If =$Mx="√" is true AND Cx < Lx is true, make the check mark red? (where x=cell number)


I know this should be intuitively obvious, but at the moment, the light isn't coming on.

Thanks for your help!
 

Attachments

  • Chandoo - Pay Stub Checker.xlsx
    35.9 KB · Views: 5
@Eloise T,

You can simply create an additional condition to test for one of the two variations. In the attached, I modified the existing condition to use green font and added a condition "=($M7="√")*($C7<$L7)" setting the font color to red.

The only thing to be careful of is the order of the conditions which will determine the precedent.

I hope that helps.

Regards,
Ken
 

Attachments

  • Chandoo - Pay Stub Checker_KenU.xlsx
    35.3 KB · Views: 9
KenU said:
@Eloise T,

You can simply create an additional condition to test for one of the two variations. In the attached, I modified the existing condition to use green font and added a condition "=($M7="√")*($C7<$L7)" setting the font color to red.

The only thing to be careful of is the order of the conditions which will determine the precedent.

I hope that helps.

Regards,
Ken
Click to expand...

Please elaborate if you will on your statement, "The only thing to be careful of is the order of the conditions which will determine the precedent."

Are you saying the order the formulas in the Conditional Formatting make a difference? If so, why?
 
The order can make a difference if there are multiple rules that could both apply. The way that the formula for the green font is written (in this case) would create that problem if that formula is given a higher position in the conditional formatting dialog.

This could be corrected by being more explicit in the formula for the green font. Such as "=($M7="√")*($C7>=$L7)" in which there is no longer any possibility for both conditions to be satisfied.

I hope that helps.

Regards,
Ken
 
"=($M7="√")*($C7>$L7)" can only be true if the contents of C7 is greater than the contents of L7 and...
"=($M7="√")*($C7<$L7)" can only be true if the contents of C7 is less than the contents of L7.
Sorry if I'm being dense but, when would there ever be a possibility of both conditions being true?
 
The formula in your original workbook was " =$M7="√" " (without the additional criteria). In the workbook I had uploaded, I left that formula as it was and applied the green font to it (meaning that it would turn all of the "√" green). The added condition "=($M7="√")*($C7<$L7)" would change the "√" to red when the value in column C is less than column L.

Perhaps that was not your real intent. Looking at your formulas, it appears that you intend for the "$C7 = $L7" case to remain black font:
"=($M7="√")*($C7>$L7)" can only be true if the contents of C7 is greater than the contents of L7 and...
"=($M7="√")*($C7<$L7)" can only be true if the contents of C7 is less than the contents of L7.
Click to expand...

So, my uploaded version would not suit your needs. Changing to the formulas you showed would.

Hope that helps.

Regards,
Ken
 
Thanks bob and Ken.

95%+ of the time, Cₓ = Lₓ, which for me is good.
It's the small percentage that I want the Cond. Form. to flag the cell and indicate with red and green that Cₓ > Lₓ or Cₓ < Lₓ.

New question: The * between =($M7="√") and ($C7<$L7) indicates what? "Multiply?" "Apply?" Also, do you know of a web site that shows Excel exceptions like: CTL+5 for crossing out, *, $, & for concatenate, etc?
 
The * is simply to multiply the two conditions in parentheses. The conditional formatting formula should evaluate to either TRUE or FALSE. Each of the comparisons in parentheses would do just that. Multiplying these together does a couple of very convenient things. First, it coerces each TRUE value to 1 and each FALSE to 0 allowing the multiplication. Second it interprets the result (0 or 1) as the required TRUE (if 1) or FALSE (if 0).

Effectively, "=($M7="√")*($C7<$L7)" would be equivalent to "=AND($M7="√",$C7<$L7)"

I hope that helps.

Thank you for link.

Regards,
Ken
 
You must log in or register to reply here.
Back
Top