Multiple Criteria for Conditional Formatting

[Guest]

Hello,

I have an existing spreadsheet that is used for different types of loan
accounts. Each of the different loan types are assigned a number designating
the type of loan it is. For examlpe; a residential loan may be a "type 3" and
a commercial loan a "type 5". The types can also be broken down further such
as a type 3 (residential loan) could have a payment type of a fixed or
adjustable interest loan. This spreadsheet uses Conditional Formatting for 2
different critieria; if a loan that is a type 3 (no matter what kind of
payment type) and has current loan balance of $500,000 to $999,999.99 then
the font in that row the font should be blue, if it is >=1,000,000 then the
font is green. At the end of the spreadsheet there is a column that contains
a formula that also looks at the balance criteria and will enter the text of
"ILR" or "N-ILR" which determines who is responsible to review the loan.
Currently I have the following formula's in the Conditional Formatting dialog
boxes:
Condition 1: =AND($G2>=500000,$G2<=999999.99,$N2=3) - blue font
Condition 2: =AND($G2>=1000000,$N2=3) - green font
I was just asked to add a third criteria that if any type 3 loan that has a
payment type (payment types are contained in column E) of INTF that has a
balance >=500,000 then the font in that row should be green. If I try to
simply add a 3rd criteria as Condition 3 within the CF dialog box with the
formula of:
=AND($G2>=500000,$N2=3,$E2="INTF")
nothing happens because the 3rd condition is ignored as soon as the first
condition is met. I'm assuming that I need to create one formula within the
condition 1 field to accomplish what I need to do; but how can I do that?
What would the formula be. I hope I explained this issue without too much
confusion. If further clarification is needed please post it and I will
reply. Any help with this issue will be greatly appreciated. Thank you.

Dave Y

 

[intruder9]

Am I missing something or why could this not be your first condition

 

[Beege]

Dave
This combines your second and third criteria:

=OR(AND($G2>=500000,$N2=3,$E2="INTF"),AND($G2>=1000000,$N2=3))
and format for green font.

And this allows thae first criterion to have an exception:

=AND($G2>=500000,$G2<=999999.99,$N2=3,NOT($E2="INTF"))

HTH

Beege

 

[daddylonglegs]

I'd have thought that for first condition you just need

=AND($N2=3,OR(AND($G2>=500000,$E2="INTF"),$G2>=1000000,))

and format for green font.

Then simply for the second

=AND($G2>=500000,$N2=3)

format blu

 

[Guest]

Hi Beege,

Thanks you for your reply. The formula you provided seems to have worked
perfectly. I also want to thank intruder9 and daddylonglegs for their replies
as well. I greatly appreciate the help.

Dave Y