Multiple Criteria for Conditional Formatting

[Dave Y]

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


"Dave Y" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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 blue

[Dave Y]

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

"Beege" wrote:

> 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
>
>
> "Dave Y" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>