Conditional Formula

[vance1167]

Hi,

I'm trying to write a conditional formula for when C10="Y" and N10>5, the font turns red. This is what I have and I keep getting an error:

=IF(C10="Y",IF(N10>5))

Thank you

[BadlySpelledBuoy]

=and(c10="y",n10>5)

[vance1167]

Thank you!

Sorry I forgot to add that I also want it to turn red if C10=" " and N10>10.

Would it be something like this?

=AND(C10="y",N10>5)OR C10=" ",N10>10)

[BadlySpelledBuoy]

=OR(AND(C10="Y",N10>5),AND(C10=" ",N10>10))

I think... Not tested it.

[vance1167]

That didn't work right. It made numbers red that were under 5.

[vance1167]

Could it be because I have my font set as "0;[Black]0?"

[BadlySpelledBuoy]

It works for me. Could you post some dummy data in a worksheet where it's not working?

[vance1167]

Everything is red.

[BadlySpelledBuoy]

Your data doesn't start on row 10, it starts on row 4.
Change the conditional formatting formula to =OR(AND(C4="Y",N4>5),AND(C4=" ",N4>10)) and see if that works.

[vance1167]

I just realized I was referencing the wrong cells in the top formula, but I fixed that and dragged the formatting through all the cells and it still didn't work.

[vance1167]

I checked the top row and did 21 days and it's still not red.

[BadlySpelledBuoy]

OK, after a little digging around, I've found the problem. Your NETWORKDAYS formulas in column N appear to be the wrong way round. i.e. you have them end date to start date rather than start date to end date. This gives negative results and therefore the conditional formatting doesn't fire.

Try this in N4 and copy down:

=IF(L4=0,"",IF(ISNUMBER(Q4),NETWORKDAYS(L4,Q4),NETWORKDAYS(L4,TODAY())))

Does that work?

[vance1167]

It works!! Thank you so much for your help with this! Made my day a lot better.

[BadlySpelledBuoy]

No problem. Happy to help.

Please mark the thread as solved if you're happy you have a full solution.

BSB.