Solved Combining 2 if(or) functions

September 10, 2016 at 11:53:01
Specs: Windows 7
I am trying to combine 2 if(or) functions but can't seem to figure it out.

I currently have them on 2 different columns but need to combine the formulas to one column so I can then use an autofilter in a macro to filter out all true results.

Here are the 2 formulas:
#1 =IF(OR(J7<NOW()+13,E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(W7="Purchase"))

#2 =IF(OR(L7>45,E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(W7="Refinance"))

any help would be greatly appreciated :-)


See More: Combining 2 if(or) functions

Reply ↓  Report •


✔ Best Answer
September 13, 2016 at 16:53:54
The WORKDAY() function should work. Something like:

=OR(AND(OR(J8<WORKDAY(TODAY(),16),E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Purchase"),AND(OR(L8>44,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Refinance"))

If you want to include Holidays,just add the Holiday range
to the end:

=WORKDAY(TODAY(),16,Holidays)

MIKE

http://www.skeptic.com/



#1
September 10, 2016 at 12:00:26
Just a quick guess, but try this:

=IF(OR(J7<NOW()+13,L7>45,E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(OR(W7="Purchase",W7="Refinance")))

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
September 10, 2016 at 12:31:54
This almost worked except for the 2 mathematical parts as they are contingent on whether or not it is a purchase or a refinance.

if it is a purchase I have to evaluate J7<NOW()+13 but L7>45 does not apply
if it is a refinance I have to evaluate L7>45 but J7<NOW()+13 does not apply

all other logical tests do apply to both purchase and refinance.


Reply ↓  Report •

#3
September 10, 2016 at 13:02:30
Try combining the J7<NOW()+13 and the W7="Purchase" in an AND()
then the L7>45 and the W7="Refinance" in another AND()
then OR() the two sections

Something like:

=IF(AND(E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""),AND(OR(AND(J7<NOW()+13,W7="Purchase"),AND(,L7>45,W7="Refinance"))))

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Related Solutions

#4
September 10, 2016 at 13:05:12
I got it to work using this formula:

=OR(AND(OR(J7<NOW()+13,E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Purchase"),AND(OR(L7>45,E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Refinance"))


Reply ↓  Report •

#5
September 10, 2016 at 13:13:17
Glad you got it solved, here is another try that is similar to your solution,
but it checks the E7 cell only once.

=IF(OR(AND(J7<NOW()+13,W7="Purchase"),AND(,L7>45,W7="Refinance")),AND(E7="PENDDECL",E7="NOIA",E7="PENDCI",K7="",R7=""))

Getting all the ANDs and ORs in the correct place is a pain.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#6
September 13, 2016 at 14:18:48
I was able to use a formula that worked but just realized that I need to count business days only.

Here is my formula:
=OR(AND(OR(J8<NOW()+16,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Purchase"),AND(OR(L8>44,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Refinance"))

Is there a way to make the J8<NOW()+16 only count business days?


Reply ↓  Report •

#7
September 13, 2016 at 16:53:54
✔ Best Answer
The WORKDAY() function should work. Something like:

=OR(AND(OR(J8<WORKDAY(TODAY(),16),E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Purchase"),AND(OR(L8>44,E8={"PENDDECL","NOIA","PENDCI"},K8="",R8=""),W8="Refinance"))

If you want to include Holidays,just add the Holiday range
to the end:

=WORKDAY(TODAY(),16,Holidays)

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#8
September 13, 2016 at 19:20:49
Thanks, final working formula:

=OR(AND(OR(J7<WORKDAY(TODAY(),16,Validation!F12:F22),E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Purchase"),AND(OR(L7>44,E7={"PENDDECL","NOIA","PENDCI"},K7="",R7=""),W7="Refinance"))


Reply ↓  Report •


Ask Question