SOLVED

# Formula issues if and

Copper Contributor

# Formula issues if and

I’m trying to write an if and formula that considers 5 different cell values for my yes / no output. I have three of them handled, but am hung up on two of the others. My current formula says “=if(and(N2=“real”, o2=“conditional”, CP2=“”),”YES”,”NO”) I need to add Y2 is not blank and if R2 is within 16 days of todays date. Every time I try to do it, I mess up my output.

6 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Formula issues if and

=if(and(N2=“real”, o2=“conditional”, CP2=“”,NOT(ISBLANK(Y2)),R2>TODAY()-16,R2<TODAY()+16),”YES”,”NO”)
or alternative options using * instead of AND and <>"" instead of NOT(ISBLANK()) and ABS()< instead of comparing each:
=if( (N2=“real”)*(o2=“conditional”)*(CP2=“”)*(Y2<>"")*(ABS(R2-TODAY())<16),”YES”,”NO”)

# Re: Formula issues if and

I must be entering it in wrong. I receive an error when I try either of those. The top one just outputs an error and the bottom gives me the something is wrong with the formula pop up and doesn’t give an output.

# Re: Formula issues if and

my apologies, in each case TODAY must have a () after it. I updated the formulas above.

# Re: Formula issues if and

That is FANTASTIC!!! Thank you so much

# Re: Formula issues if and

=IF(AND(O2=“Real Deal”,P2=“Conditional Approved”,CR2=“”,NOT(ISBLANK(AB2))*S2>(TODAY()+16)),”YES”,”NO”)

I need the formula to pick up either conditional approved or approved w/condition in column P. How can I change it to do either or

# Re: Formula issues if and

=IF(AND(O2=“Real Deal”,OR(P2=“Conditional Approved”,P2=“Approved w/Condition”),CR2=“”,NOT(ISBLANK(AB2))*S2>(TODAY()+16)),”YES”,”NO”)
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Formula issues if and

=if(and(N2=“real”, o2=“conditional”, CP2=“”,NOT(ISBLANK(Y2)),R2>TODAY()-16,R2<TODAY()+16),”YES”,”NO”)
or alternative options using * instead of AND and <>"" instead of NOT(ISBLANK()) and ABS()< instead of comparing each:
=if( (N2=“real”)*(o2=“conditional”)*(CP2=“”)*(Y2<>"")*(ABS(R2-TODAY())<16),”YES”,”NO”)